SQL subqueries and CTEs help students write queries that answer multi-step questions from relational databases. This cheat sheet explains how to place one query inside another, how to name temporary result sets, and when each pattern is useful. Students need these tools to filter groups, compare rows to aggregates, and organize complex database logic clearly.
The most important ideas are that a subquery returns data used by an outer query, while a CTE creates a named temporary table for one statement. Common patterns include WHERE column IN (subquery), WHERE EXISTS (subquery), and WITH cte_name AS (SELECT ...). Correlated subqueries depend on the current row of the outer query, and recursive CTEs can follow hierarchies such as folders, managers, or graph paths.
Key Facts
- A subquery is a SELECT statement nested inside another SQL statement, such as SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments).
- A scalar subquery must return exactly one value, such as SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products).
- The IN operator checks whether a value matches any value returned by a subquery, as in WHERE department_id IN (SELECT id FROM departments WHERE region = 'West').
- The EXISTS operator returns true when the subquery returns at least one row, as in WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id).
- A correlated subquery uses a column from the outer query, so it may run once for each row considered by the outer query.
- A CTE uses the pattern WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name; to make a complex query easier to read.
- Multiple CTEs can be defined in one WITH clause by separating them with commas, such as WITH a AS (...), b AS (...) SELECT ... FROM b.
- A recursive CTE has a base query and a recursive query joined by UNION ALL, and it must include a stopping condition to avoid infinite recursion.
Vocabulary
- Subquery
- A query nested inside another SQL query to provide values, rows, or a temporary result for the outer query.
- Outer query
- The main SQL query that contains or uses the result of a subquery.
- Correlated subquery
- A subquery that refers to a column from the outer query and is evaluated in relation to each outer row.
- CTE
- A common table expression, written with WITH, that gives a temporary name to a query result for use in one SQL statement.
- EXISTS
- A SQL condition that is true if its subquery returns one or more rows.
- Recursive CTE
- A CTE that refers to itself to repeatedly build results, often used for hierarchies or paths.
Common Mistakes to Avoid
- Using = with a subquery that returns many rows is wrong because = expects one value; use IN when the subquery can return multiple values.
- Forgetting to alias tables in correlated subqueries is wrong because SQL may not know which table a column belongs to; use clear aliases like c for customers and o for orders.
- Selecting unnecessary columns inside EXISTS is inefficient and confusing because EXISTS only checks whether rows exist; use SELECT 1 or another simple constant.
- Writing a CTE and expecting it to persist is wrong because a CTE lasts only for the single SQL statement that follows the WITH clause.
- Creating a recursive CTE without a stopping condition is wrong because the query may repeat forever or stop only when the database recursion limit is reached.
Practice Questions
- 1 Write a query to find products whose price is greater than the average price in the products table.
- 2 Given students(id, name) and enrollments(student_id, course_id), write a query using IN to list students enrolled in course_id = 12.
- 3 Given customers(id, name) and orders(id, customer_id), write a query using EXISTS to list customers who have at least one order.
- 4 Explain when a CTE is easier to use than a nested subquery, and describe one situation where a recursive CTE would be useful.