Lumina

Manuals / SQL / Ch 7

B · AnalyzeIntermediate40 min read

7. Subqueries & CTEs

SQL · 48 pages source format

Subqueries in WHERE/FROM. WITH clause (CTE) for readable multi-step queries. Prefer CTEs over nested subqueries when clarity matters.

What you'll learn

  • Subqueries in WHERE
  • Subqueries in FROM
  • WITH ... AS (CTE)
  • EXISTS

Subquery in WHERE

WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000) — find high-value customers.

Do this now

Products never ordered: NOT IN or NOT EXISTS pattern.

Clear?

CTEs

WITH high_value AS (SELECT ... ) SELECT * FROM high_value — name intermediate results.

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT * FROM monthly_revenue ORDER BY month;

Do this now

Rewrite a nested subquery as a CTE.

Clear?

EXISTS

EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) — often faster than IN for large sets.

Do this now

Customers with at least one order using EXISTS.

Clear?

Checklist