Skip to content

Instantly share code, notes, and snippets.

@ilovejs
Created July 12, 2018 02:19
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ilovejs/2116669346ce2b62f9be5c7d2be11077 to your computer and use it in GitHub Desktop.
--https://www.citusdata.com/blog/2018/05/15/fun-with-sql-recursive-ctes/
--Return: x
WITH RECURSIVE tens(x) AS (
SELECT 10
UNION
SELECT x + 10 FROM tens WHERE x + 10 <= 100
)
SELECT x FROM tens;
--Without having to drop down to a procedural language like plpgsql or plv8.
--Ex.1
DROP TABLE employees;
CREATE TABLE employees (
id serial PRIMARY KEY,
name varchar(255),
manager_id int
);
INSERT INTO employees VALUES (1, 'Umur', null);
INSERT INTO employees VALUES (2, 'Craig', 1);
INSERT INTO employees VALUES (3, 'Daniel', 2); --2
INSERT INTO employees VALUES (4, 'Claire', 1); --since this row get skipped, we would never get to id(10) Teresa
INSERT INTO employees VALUES (5, 'Lindsay', 2); --3
INSERT INTO employees VALUES (6, 'Will', 2); --4
INSERT INTO employees VALUES (7, 'Burak', 2); --5
INSERT INTO employees VALUES (8, 'Eren', 2); --6
INSERT INTO employees VALUES (9, 'Katie', 3);
INSERT INTO employees VALUES (10, 'Teresa', 4);
WITH RECURSIVE print_manager_of_children AS (
SELECT id, name, manager_id FROM employees WHERE id = 2 --change to 2, 3 :)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN print_manager_of_children pm ON pm.id = e.manager_id --can't be (outer) left join
)
SELECT id, "name", manager_id FROM print_manager_of_children;
/*
id |name |manager_id |
---|--------|-----------|
2 |Craig |1 |
3 |Daniel |2 |
5 |Lindsay |2 | <- id(2) is missing
6 |Will |2 |
7 |Burak |2 |
8 |Eren |2 |
9 |Katie |3 |
*/
--Zoom in example
WITH RECURSIVE print_manager AS (
--Teresa
SELECT id, name, manager_id FROM employees WHERE id = 10
UNION
--Plus, any emploree who has Teresa as manager. (T's id has been used as e.manager_id)
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN print_manager pm --First, it use Teresa as table resource to loop.
ON pm.id = e.manager_id --future row start from 8th, won't contains any id = 2...so it stopped.
)
SELECT id, "name", manager_id FROM print_manager;
/*
id |name |manager_id |
---|-----|-----------|
8 |Eren |2 |
*/
--Iterative Explaination
WITH RECURSIVE print_manager AS (
--Teresa
SELECT id, name, manager_id FROM employees WHERE id = 2
UNION
--Plus, any emploree who has Teresa as manager. (T's id has been used as e.manager_id)
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN print_manager pm --First, it use Teresa as table resource to loop.
ON pm.id = e.manager_id --future row start from 8th, won't contains any id = 2...so it stopped.
)
SELECT id, "name", manager_id FROM print_manager;
/*
id |name |manager_id |
---|--------|-----------|
2 |Craig |1 | <-- start
3 |Daniel |2 | Row 3-8 is generated based on 'Is there any emp using id(2) as e.manager_id ?
5 |Lindsay |2 |
6 |Will |2 |
7 |Burak |2 |
8 |Eren |2 |
9 |Katie |3 | After check id(2), row 3 was used to check.
Row 5 was not used
* */
--Better choice, this is left join and never miss a row in left table.
SELECT
e.id,
e.name,
m.id AS manager_id,
m."name" AS manager_name
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
/*
id |name |manager_id |manager_name |
---|--------|-----------|-------------|
1 |Umur | | |
2 |Craig |1 |Umur |
3 |Daniel |2 |Craig |
4 |Claire |1 |Umur |
5 |Lindsay |2 |Craig |
6 |Will |2 |Craig |
7 |Burak |2 |Craig |
8 |Eren |2 |Craig |
9 |Katie |3 |Daniel |
10 |Teresa |4 |Claire |
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment