Subqueries and CTEsLesson 5.3

CTEs with WITH — making complex queries readable

CTE syntax, WITH keyword, multiple CTEs, CTE vs subquery readability, CTE scope, chaining CTEs

CTEs Name Your Intermediate Results

A Common Table Expression (CTE) lets you name a subquery and reference it by name in the main query. CTEs make complex queries dramatically easier to read and debug — you can test each named part independently.

WITH
  completed_orders AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
  ),
  top_customers AS (
    SELECT customer_id
    FROM completed_orders
    WHERE total > 1000
  )
SELECT c.name, co.total
FROM customers c
JOIN completed_orders co ON c.id = co.customer_id
WHERE c.id IN (SELECT customer_id FROM top_customers)
ORDER BY co.total DESC;

CTEs vs Subqueries

CTEs and subqueries in FROM are equivalent in most databases — they produce the same execution plan. Choose CTEs when a result is referenced more than once or when building multi-step logic. Choose inline subqueries for simple, one-off cases. CTEs cannot be referenced outside their WITH block.