Subqueries and CTEs

As your SQL needs become more complex, you will often need to use the results of one query as the input for another. There are two primary ways to do this: Subqueries and Common Table Expressions (CTEs).

Subqueries

A subquery (or nested query) is a query embedded within another query.

In the WHERE Clause

This is the most common use of a subquery.

-- Find users who have placed an order greater than $1000
SELECT first_name, last_name
FROM users
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE total_amount > 1000
);

In the SELECT Clause

You can use a subquery to calculate a value on the fly for each row.

SELECT 
    first_name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

(This is called a Correlated Subquery because the inner query references a column (users.id) from the outer query. It executes once for every row returned by the outer query, which can be slow on large datasets).

Common Table Expressions (CTEs)

A CTE is a temporary, named result set created by a WITH clause that exists only for the duration of a single query.

CTEs are heavily preferred over subqueries for complex logic because they make the code much more readable by breaking it down into sequential, logical steps (DRY).

WITH high_value_orders AS (
    SELECT user_id, total_amount
    FROM orders
    WHERE total_amount > 1000
),
user_summaries AS (
    SELECT user_id, COUNT(*) as order_count
    FROM high_value_orders
    GROUP BY user_id
)
SELECT u.first_name, s.order_count
FROM users u
JOIN user_summaries s ON u.id = s.user_id;

Recursive CTEs

CTEs have a superpower: they can refer to themselves. This is called a Recursive CTE, and it is the standard way to query hierarchical data (like an organizational chart, or a comment thread with nested replies).

WITH RECURSIVE org_chart AS (
    -- Anchor member (the CEO)
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member (find subordinates)
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;