Lesson 5: Use a CTE to name a query step
A common table expression exists for one statement and gives an intermediate result a name. It improves readability but does not guarantee materialization or faster execution.
WITH customer_totals AS (
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
)
SELECT customer_id, spent
FROM customer_totals
WHERE spent > 1000;Subquery patterns
8 patterns shown
| Pattern | Purpose | Example |
|---|---|---|
| Scalar subquery | One value used in a comparison or select expression. | WHERE total > (SELECT AVG(total) FROM orders) |
| IN subquery | Compare a value to a one-column result set. | WHERE customer_id IN (SELECT id FROM vip_customers) |
| EXISTS | Return a row when a related row exists. | WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id) |
| NOT EXISTS | Return rows with no related match. | WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id) |
| Correlated subquery | References a row from the outer query. | o.customer_id = c.id inside the inner query. |
| Derived table | Use a subquery in FROM as a named result. | FROM (SELECT customer_id,SUM(total) spent FROM orders GROUP BY customer_id) s |
| WITH / CTE | Name one or more query steps for a single statement. | WITH recent AS (SELECT * FROM orders WHERE order_date>CURRENT_DATE-30) SELECT * FROM recent |
| Recursive CTE | Walk hierarchies or sequences using an anchor and recursive term. | WITH RECURSIVE tree AS (...) SELECT * FROM tree |
Choose EXISTS or a join deliberately
Use EXISTS when the question is whether a related row exists; it avoids multiplying outer rows when multiple matches are present. Use a join when columns from both sides are required. Compare plans and results for real data before changing query shape.
Progress stays in this browser.