Subqueries and Common Table Expressions
Nesting a query inside another to answer a question a single flat query can't, and CTEs — the readable, nameable alternative for anything beyond a trivial subquery.
What you'll learn
- Write a subquery in a WHERE clause to filter based on a computed set of values
- Rewrite a nested subquery as a WITH (CTE) for clarity
- Explain when a CTE genuinely improves a query over an equivalent subquery, and when it's just a style preference
Prerequisites
Explanation
This lesson's exercises run in this sandbox's real SQL runner, which is SQLite, not PostgreSQL. Subquery and WITH/CTE syntax is genuinely dialect-compatible between the two, so what you build and run here behaves the same way in a real PostgreSQL database.
A subquery is a complete SELECT nested inside another query, most commonly inside a WHERE clause: SELECT * FROM books WHERE author_id IN (SELECT id FROM authors WHERE country = 'Nigeria') first resolves the inner query to a set of author IDs, then uses that set to filter books. This answers a genuinely different kind of question than a join alone can: "books whose author matches some condition" without needing every one of that author's columns in the final result, and without risking the row-multiplication a join can introduce when the relationship isn't strictly one-to-one.
A Common Table Expression (CTE), written with WITH name AS (SELECT ...), defines a named, temporary result set at the top of a query that the rest of the query can reference by name, as if it were a real table: WITH nigerian_authors AS (SELECT id FROM authors WHERE country = 'Nigeria') SELECT * FROM books WHERE author_id IN (SELECT id FROM nigerian_authors). For this simple case, the CTE and the plain subquery are functionally equivalent — the real payoff shows up as a query grows: a CTE gives an intermediate result set a readable name, can be referenced multiple times in the same query without repeating its logic, and — critically — cannot be nested more than one level deep visually, which keeps a complex, multi-step query readable as a sequence of named steps rather than a subquery buried inside a subquery inside a subquery.
For genuinely simple, single-use filtering, a plain subquery and an equivalent CTE are functionally the same, and choosing between them is largely a readability preference. The CTE's real advantage appears specifically when: the same intermediate result is needed more than once in the query, the logic is complex enough that a descriptive name meaningfully aids understanding, or — PostgreSQL specifically also supports recursive CTEs (WITH RECURSIVE), which can express genuinely different queries a plain subquery cannot express at all, such as walking an arbitrary-depth hierarchy (an org chart, a category tree) in a single query — a capability worth knowing exists even though this introductory lesson's exercises stay with non-recursive CTEs.
Example
The same result via a plain subquery, then via an equivalent, more readable CTE -- both genuinely run here.
-- Plain subquery version:
SELECT title, price
FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'Nigeria');
-- Equivalent CTE version -- for this simple case, purely a readability choice:
WITH nigerian_authors AS (
SELECT id FROM authors WHERE country = 'Nigeria'
)
SELECT title, price
FROM books
WHERE author_id IN (SELECT id FROM nigerian_authors);Try it yourself
Change the country filter inside the CTE to a different country and see the results change.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Write a query using a subquery in WHERE to return the titles of books priced ABOVE the average price of all books (do not hard-code the average -- compute it with a subquery).
Checks: Returns only books priced above the computed average
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Using a CTE, write a query that finds every author whose books have a combined total value (SUM of price across all their books) greater than 30, returning the author's name and that total. Name your CTE author_totals.
Checks: Returns exactly the authors whose combined book value exceeds 30, with the correct totals
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Hard-coding a computed value (like an average) instead of using a subquery to compute it dynamically -- this silently becomes wrong the moment the underlying data changes.
- Nesting subqueries several levels deep instead of extracting intermediate steps into named CTEs -- deeply nested subqueries are notoriously hard to read and debug, exactly the problem CTEs exist to solve.
- Repeating the same subquery logic multiple times in one query instead of defining it once as a CTE -- besides being harder to read, this risks the two copies silently drifting out of sync if one is edited without the other.
Knowledge check
Takeaway
A subquery and an equivalent CTE are functionally the same for simple, single-use cases — reach for a CTE once the same intermediate result is needed more than once, or once naming an intermediate step would make a complex query meaningfully more readable than a nested subquery.
Summary
A subquery nests a complete SELECT inside another query, commonly in WHERE. A CTE (WITH name AS (...)) names an intermediate result set that the rest of the query can reference, avoiding deep nesting and repeated logic. PostgreSQL's recursive CTEs (WITH RECURSIVE) can express hierarchy traversal a plain subquery cannot.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.