Set Operations, CTEs, and Recursion

Set operations

Set operations combine the results of two queries that have the same number of columns with compatible types.

Operator	Result
`UNION`	rows in either query, duplicates removed
`UNION ALL`	rows in either query, duplicates kept (faster)
`INTERSECT`	rows in both queries
`EXCEPT`	rows in the first query but not the second (`MINUS` in Oracle)

Example 1: UNION vs UNION ALL

SQL

SELECT city FROM customers
UNION
SELECT 'Chennai'
ORDER BY city;

Output

   city    
-----------
 Bengaluru
 Chennai
 Delhi
 Mumbai
 Pune
(5 rows)

SQL

SELECT city FROM customers WHERE id <= 4
UNION ALL
SELECT city FROM customers WHERE id <= 4
ORDER BY city;

Output

   city    
-----------
 Bengaluru
 Bengaluru
 Bengaluru
 Bengaluru
 Delhi
 Delhi
 Mumbai
 Mumbai
(8 rows)

Use UNION ALL unless you need duplicates removed. UNION has to sort or hash to find them.

Example 2: INTERSECT

Customers who have both delivered and shipped orders.

SQL

SELECT customer_id FROM orders WHERE status = 'delivered'
INTERSECT
SELECT customer_id FROM orders WHERE status = 'shipped';

Output

 customer_id 
-------------
           1
(1 row)

Example 3: EXCEPT

Customers with orders but none delivered.

SQL

SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM orders WHERE status = 'delivered'
ORDER BY customer_id;

Output

 customer_id 
-------------
           4
           5
(2 rows)

Example 4: combine different tables

SQL

SELECT name, 'employee' AS kind FROM employees WHERE id <= 2
UNION ALL
SELECT name, 'customer' FROM customers WHERE id <= 2;

Output

 name  |   kind   
-------+----------
 Meera | employee
 Ravi  | employee
 Aarav | customer
 Isha  | customer
(4 rows)

Common table expressions (CTEs)

A CTE names a subquery with WITH, so a long query can be read top to bottom.

Example 5: a basic CTE

SQL

WITH dept_avg AS (
    SELECT dept_id, avg(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.name, e.salary, round(a.avg_salary) AS dept_avg
FROM employees e
JOIN dept_avg a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_salary
ORDER BY e.id;

Output

  name  | salary | dept_avg 
--------+--------+----------
 Meera  | 250000 |   168000
 Ravi   | 180000 |   168000
 Divya  | 160000 |   111667
 Vikram | 120000 |   100000
(4 rows)

Example 6: several CTEs

Each CTE can use the ones defined before it.

SQL

WITH order_totals AS (
    SELECT o.id, o.customer_id, sum(p.price * oi.quantity) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.id, o.customer_id
),
customer_totals AS (
    SELECT customer_id, sum(total) AS spent
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.name, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
ORDER BY ct.spent DESC;

Output

  name  |  spent  
--------+---------
 Aarav  | 1075.00
 Karan  | 1070.00
 Isha   |  460.00
 Lata   |  200.00
 Manish |  125.00
(5 rows)

Example 7: a CTE used twice

A CTE is defined once and can be referenced several times. Departments above the average department payroll:

SQL

WITH payroll AS (
    SELECT dept_id, sum(salary) AS total
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT d.name, p.total
FROM payroll p
JOIN departments d ON d.id = p.dept_id
WHERE p.total > (SELECT avg(total) FROM payroll)
ORDER BY p.total DESC;

Output

    name     | total  
-------------+--------
 Engineering | 840000
(1 row)

Since PostgreSQL 12, a CTE that is referenced once is inlined into the query like a subquery. Use WITH x AS MATERIALIZED (...) to force it to run once.

Recursive CTEs

A recursive CTE has a starting query and a recursive part that refers to the CTE itself. It repeats until the recursive part returns no rows. It is the standard way to walk hierarchies such as org charts and category trees.

Example 8: the management chain of one employee

Start at Arjun and walk up to the top.

SQL

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = 'Arjun'
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT level, name FROM chain ORDER BY level;

Output

 level | name  
-------+-------
     1 | Arjun
     2 | Kiran
     3 | Ravi
     4 | Meera
(4 rows)

Example 9: everyone under a manager

Walk down from Ravi to all direct and indirect reports.

SQL

WITH RECURSIVE reports AS (
    SELECT id, name, 0 AS depth
    FROM employees
    WHERE name = 'Ravi'
    UNION ALL
    SELECT e.id, e.name, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
)
SELECT repeat('  ', depth) || name AS org_chart
FROM reports
ORDER BY depth, name;

Output

 org_chart 
-----------
 Ravi
   Kiran
   Neha
     Arjun
(4 rows)

Example 10: generate a sequence

SQL

WITH RECURSIVE n(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

Output

 i 
---
 1
 2
 3
 4
 5
(5 rows)

Without a stopping condition (WHERE i < 5 here), or with a cycle in the data, the query never ends. PostgreSQL also has generate_series(1, 5) for this.

Summary

Use UNION ALL unless duplicates must be removed.
EXCEPT is "in A but not in B". INTERSECT is "in both".
A CTE names a step. A recursive CTE walks a tree.