Subqueries & CTEs

Use subqueries in FROM and WHERE clauses, and organize complex queries with Common Table Expressions.

IN Subquery

SELECT id, name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- alice, bob, carol (have orders)

SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- dave (no orders)

Derived Tables (FROM Subquery)

SELECT customer_id, total
FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
ORDER BY customer_id;
-- 10: 300.0, 20: 200.0, 30: 300.0

Nested Subqueries

SELECT COUNT(*) FROM orders
WHERE customer_id IN (
  SELECT id FROM customers
  WHERE name IN ('alice', 'bob')
);
-- 4

Common Table Expressions (WITH)

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 200
ORDER BY total DESC;