MIP Logo

Recursive query in SQL

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

A recursive common table expression(CTE) is a CTE that references itself. It is useful in querying hierarchical data.

Lets take  at an example of hierarchical data in a table

Job_key obj_key name
1 Main_1
1 2 load_1
1 3 load_2
1 4 load_3
2 5 load_4
2 6 load_5
3 7 load_6
5 8 load_7
5 9 load_8
5 10 load_9
11 Main_2
11 12 DS_1
11 13 DS_2
11 14 DS_3
11 15 DS_4
13 16 DS_5
13 17 DS_6

The above table stores ETL job (jobs for loading/transforming data) information. One job can have multiple job and the child job can have more jobs/tasks within them.

So if we need to create a hierarchical structure like

Root job->job1->job2.   we can use a recursive query

 

 

 

 

 

Share this post