Subqueries & CTEs: Complex Query Logic

As your data requirements grow, simple joins and aggregations won't be enough. Subqueries and Common Table Expressions (CTEs) allow you to write modular and readable queries.

1. Subqueries

A subquery is a query nested inside another query. They can be used in SELECT, FROM, or WHERE.

sql code
-- Find employees who earn more than the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. Common Table Expressions (CTEs)

CTEs provide a more readable alternative to subqueries. They define a temporary result set that you can reference within another query.

sql code
WITH regional_sales AS (
    SELECT region, SUM(amount) as total
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM regional_sales
WHERE total > 100000;

3. Recursive CTEs

Recursive CTEs are used to query hierarchical data where the number of levels is unknown (e.g., organizational charts or file systems).

sql code
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Alice' -- The Boss
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

Performance Consideration

CTEs in some older databases were "materialized" (calculated once and stored in memory), which could be slower or faster depending on the use case. Modern Postgres (v12+) optimizes this automatically!