Subqueries & CTEs

Scalar subquery

-- Transactions above the overall average
SELECT txn_id, amount
FROM   transactions
WHERE  amount > (SELECT AVG(amount) FROM transactions)
AND    status = 'SUCCESS';

-- Show each row vs the average
SELECT txn_id, amount,
       (SELECT ROUND(AVG(amount),2) FROM transactions) AS overall_avg,
       amount - (SELECT AVG(amount) FROM transactions)  AS diff
FROM   transactions
WHERE  status = 'SUCCESS';

Inline view (subquery in FROM)

-- Top 3 accounts by total volume
SELECT account_id, total_volume
FROM   (
    SELECT account_id,
           SUM(amount) AS total_volume
    FROM   transactions
    WHERE  status = 'SUCCESS'
    GROUP BY account_id
) account_summary
ORDER BY total_volume DESC
FETCH FIRST 3 ROWS ONLY;    -- Oracle | use LIMIT 3 in PG/MySQL

Correlated subquery

Runs once per outer row — references the outer query's column. Powerful but slow on large tables.

-- Employees earning above their own department average
SELECT e.name, e.dept, e.salary
FROM   employees e
WHERE  e.salary > (
    SELECT AVG(e2.salary)
    FROM   employees e2
    WHERE  e2.dept = e.dept    -- ← references outer row
);
-- For each employee row, inner query runs with THAT employee's dept

WITH clause — CTEs

Give your subquery a name. Reference it like a table. Chain multiple CTEs together.

-- Single CTE
WITH branch_stats AS (
    SELECT branch,
           COUNT(*) AS total_txns,
           COUNT(CASE WHEN status='SUCCESS' THEN 1 END) AS success_txns,
           SUM(amount) AS total_volume
    FROM   transactions
    GROUP BY branch
)
SELECT branch,
       ROUND(success_txns * 100.0 / total_txns, 2) AS success_rate
FROM   branch_stats
WHERE  total_txns >= 100
ORDER BY success_rate DESC;

-- Chained CTEs — each can reference the previous
WITH
account_totals AS (
    SELECT account_id, SUM(amount) AS total_spent
    FROM   transactions WHERE status='SUCCESS'
    GROUP BY account_id
),
segment_summary AS (
    SELECT a.segment,
           COUNT(DISTINCT a.account_id) AS accounts,
           SUM(t.total_spent)           AS total_volume
    FROM   accounts a
    JOIN   account_totals t ON a.account_id = t.account_id
    GROUP BY a.segment
)
SELECT * FROM segment_summary ORDER BY total_volume DESC;

Recursive CTE — org hierarchy

-- PostgreSQL / SQL Server
WITH RECURSIVE org AS (
    -- Anchor: top-level managers
    SELECT emp_id, name, manager_id, 1 AS lvl
    FROM   employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find each person's direct reports
    SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1
    FROM   employees e
    JOIN   org o ON e.manager_id = o.emp_id
)
SELECT lvl, name FROM org ORDER BY lvl, name;

-- Oracle uses CONNECT BY instead
SELECT LEVEL, name
FROM   employees
START WITH  manager_id IS NULL
CONNECT BY  PRIOR emp_id = manager_id;

EXISTS vs IN vs JOIN

-- Accounts active in 2024 — three ways
WHERE account_id IN (SELECT account_id FROM transactions WHERE txn_date >= '2024-01-01')

WHERE EXISTS (SELECT 1 FROM transactions t
              WHERE t.account_id = a.account_id
              AND   t.txn_date >= '2024-01-01')  -- preferred for large tables

-- Accounts with NO transactions (use NOT EXISTS, not NOT IN)
WHERE NOT EXISTS (SELECT 1 FROM transactions t
                  WHERE t.account_id = a.account_id)  -- NULL safe ✓