Understanding Recursive CTEs in SQL: A Simple Guide
In this video, we'll dive into the concept of Recursive Common Table Expressions (CTEs) in SQL. Recursive CTEs are powerful tools for working with hierarchical data, such as organizational charts or directory structures. We'll break down how they work, step by step, with clear examples that show you how to write and use them effectively. Whether you're new to SQL or looking to deepen your understanding, this video will help you master Recursive CTEs with ease.
-- Recursive SQL Queries
-- Basic Syntax
with cte_recur as
(
select query - base query -- ONLY executes during the FIRST Iteration
union / union all
select query - recursive query -- Executes from the SECOND iteration onwards untill the last iteration.
from cte_recur
where / ON termination condition -- When this condition fails, the recursion stops.
)
select *
from cte_recur;
-- Print numbers 1 to 10 without using any inbuilt functions
with cte as (
select 1 as num
union all
select (1+num) as num
from cte
where num (lessthan) 10
)
select * from cte;
-- 1st Iterations:
select 1 as num
union
-- 2nd Iterations:
select (num + 1) as num
from (select 1 as num) cte
where num (lessthan) 10
union
-- 3rd Iterations:
select (num + 1) as num
from ( select (num + 1) as num
from (select 1 as num) cte
where num (lessthan) 10) cte
where num (lessthan) 10
union
-- 4th Iterations:
select (num + 1) as num
from ( select (num + 1) as num
from ( select (num + 1) as num
from (select 1 as num) cte
where num (lessthan) 10) cte
where num (lessthan) 10) cte
where num (lessthan) 10
drop TABLE emp_details;
CREATE TABLE emp_details
(
id int PRIMARY KEY,
name varchar(100),
manager_id int,
salary int,
designation varchar(100)
);
INSERT INTO emp_details VALUES (1, 'Shripadh', NULL, 10000, 'CEO');
INSERT INTO emp_details VALUES (2, 'Satya', 5, 1400, 'Software Engineer');
INSERT INTO emp_details VALUES (3, 'Jia', 5, 500, 'Data Analyst');
INSERT INTO emp_details VALUES (4, 'David', 5, 1800, 'Data Scientist');
INSERT INTO emp_details VALUES (5, 'Michael', 7, 3000, 'Manager');
INSERT INTO emp_details VALUES (6, 'Arvind', 7, 2400, 'Architect');
INSERT INTO emp_details VALUES (7, 'Asha', 1, 4200, 'CTO');
INSERT INTO emp_details VALUES (8, 'Maryam', 1, 3500, 'Manager');
INSERT INTO emp_details VALUES (9, 'Reshma', 8, 2000, 'Business Analyst');
INSERT INTO emp_details VALUES (10, 'Akshay', 8, 2500, 'Java Developer');
-- 1) Find the hierarchy of employees under a given manager "Asha".
select * from emp_details;
Asha== Micheal,Arvind
Micheal== Satya, Jia, David
with cte as (
select * from emp_details where name='Asha'
union all
select e.*
from cte
join emp_details e on e.manager_id=cte.id)
select * from cte;
-- 1st Iteration
select * from emp_details
where name = 'Asha';
-- 7 "Asha" 1 4200 "CTO"
-- 2nd Iteration
-- CTE is the result returned from the previous iteration.
select e.*
from ( select * from emp_details
where name = 'Asha' ) cte
join emp_details e on e.manager_id = cte.id
--5 "Michael" 7 3000 "Manager"
--6 "Arvind" 7 2400 "Architect"
-- 3rd Iteration
select e.*
from ( select e.*
from ( select * from emp_details
where name = 'Asha' ) cte
join emp_details e on e.manager_id = cte.id) cte
join emp_details e on e.manager_id = cte.id
--2 "Satya" 5 1400 "Software Engineer"
--3 "Jia" 5 500 "Data Analyst"
--4 "David" 5 1800 "Data Scientist"
-- 4th Iteration
select e.*
from ( select e.*
from ( select e.*
from ( select * from emp_details
where name = 'Asha' ) cte
join emp_details e on e.manager_id = cte.id) cte
join emp_details e on e.manager_id = cte.id) cte
join emp_details e on e.manager_id = cte.id
-- 2) Re-write above query to display only emp_id, emp_name and manager_name
with cte as
(
select emp.id as emp_id, emp.name as emp_name, mng.name as manager_name , 1 as iteration
from emp_details emp
join emp_details mng on emp.manager_id = mng.id
where emp.name = 'Asha'
union all
select e.id, e.name, cte.emp_name , (iteration+1) as iteration
from cte
join emp_details e on e.manager_id = cte.emp_id
)
select *
from cte;
-- In Oracle:
with cte(emp_id,emp_name,manager_name) as
(
select emp.id as emp_id, emp.name as emp_name, mng.name as manager_name
from emp_details emp
join emp_details mng on emp.manager_id = mng.id
where emp.name = 'Asha'
union all
select e.id, e.name, cte.emp_name
from cte
join emp_details e on e.manager_id = cte.emp_id
)
select *
from cte;
-- In SQL Server:
with cte as
(
select emp.id as emp_id, emp.name as emp_name, mng.name as manager_name
from emp_details emp
join emp_details mng on emp.manager_id = mng.id
where emp.name = 'Asha'
union all
select e.id, e.name, cte.emp_name
from cte
join emp_details e on e.manager_id = cte.emp_id
)
select *
from cte;