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.
Understanding SQL Subqueries & CTEs
The shape of a query result matters as much as the values in it. A condition that compares one price with an average needs one value. If its inner query produces several prices, the database cannot choose which one to use and reports an error.
A filter based on a list can accept many rows, but it usually needs one column. Students should check both the number of columns and the possible number of rows before placing a query inside another. This habit prevents many confusing errors.
Empty results matter too. An empty list used with IN matches nothing. A scalar result that is missing may become NULL, and comparisons with NULL do not behave like ordinary true or false comparisons.
Correlated queries are useful when each record needs its own comparison. For example, a school database might find students whose score is above the average score within their own class. The inner calculation changes because the current class changes.
It helps to read such a query from the outside inward. First identify the current outer row. Then identify the inner rows linked to it.
Then decide what result the inner query gives for that one outer row. Database systems may optimize this work, but a correlated query can still be costly on large tables.
EXISTS is often a clear choice when the only needed fact is whether a related record is present. It avoids pretending that the actual related values are needed when they are not.
CTEs improve reasoning because they let a long task be split into stages. One stage might clean invalid records. The next might group sales by month.
A final stage might select months above a target. Each name should describe the result, not just its position, so names like monthly_sales are more useful than data2. Students can test each stage by temporarily selecting from that CTE.
This makes errors easier to locate. A CTE exists only for the statement that follows it.
It is not a saved table and it does not automatically make a query faster. Its main benefit is clear structure, though the database optimizer may choose an efficient plan.
Recursive CTEs model relationships that repeat through levels. A company chart can start with a top manager, then find direct reports, then reports of those reports. The starting rows form the base step.
The repeating part must move to a new level each time. Good recursive queries usually keep a depth value so results can be ordered and limited. Real data can contain mistakes, such as an employee recorded as their own manager or a folder placed inside one of its descendants.
Those loops can make recursion repeat forever unless the query has a safe limit or tracks visited records. When learning recursion, draw a small tree on paper and list the rows produced at each level. The drawing makes the sequence much easier to verify.