# Recursive query in SQL

> 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…
> URL: https://mip.com.au/blogs/recursive-query-in-sql/
> Last modified: 2026-08-06T04:24:28+00:00

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

 

 

 

 

 
