Subqueries & CTEs
WITH clauses, EXISTS, IN subqueries, recursive CTEs, and when to use each
Subquery & CTE vocabulary
- WITH name AS (...) — CTE: names a subquery; readable and reusable within the query
- EXISTS (subquery) — true if subquery returns any row; stops at first match
- IN (subquery) — matches if value equals any row returned; watch out for NULLs with NOT IN
- WITH RECURSIVE — for hierarchical data: org charts, trees, graphs
- CTE vs subquery: same result; CTE wins on readability for complex logic
Question 0 of 5
Read this CTE and describe what it does:WITH recent_orders AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY user_id
)
SELECT u.email, ro.cnt
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.cnt >= 3;
- WITH name AS (...) — defines a named, temporary result set used in the following query
- CTEs are like giving a subquery a readable name; they do not persist after the query ends
- Benefits over subqueries: readable (named), reusable within the same query, and some databases optimize them differently
What does EXISTS check in this query?SELECT id, email FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending'
);
- EXISTS (subquery) — returns true if the subquery returns any row at all; the subquery value does not matter (that's why
SELECT 1is idiomatic) - NOT EXISTS — returns true if the subquery returns no rows
- Correlated subquery — the inner query references
u.idfrom the outer query; it runs once per outer row
A query uses: WHERE user_id IN (SELECT id FROM users WHERE country = 'UA');. What does the IN subquery do here?
- IN (value1, value2, ...) — checks if a value matches any item in a list
- IN (SELECT id FROM ...) — same, but the list comes from a subquery
- NOT IN (subquery) — excludes rows that match; beware: if the subquery returns any NULL, NOT IN returns no rows (SQL NULL semantics)
This query uses a recursive CTE. What does it do?WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
- WITH RECURSIVE — enables recursive CTEs for traversing hierarchical data
- Anchor member — the first SELECT (seed): starts with employee id=1
- Recursive member — the second SELECT joined to the CTE itself; adds employees managed by already-found employees
- UNION ALL — combines anchor + recursive results; runs until no new rows are added
How would you describe the difference between a CTE and a subquery in a code review comment?
- "Refactor this subquery into a CTE for readability" — when a subquery is used more than once or is complex
- "This CTE is used once — an inline subquery is fine" — when simplicity is preferred
- "Consider a CTE here — the inline subquery makes this hard to follow"