Some questions cannot be answered in a single simple step. "Products priced above the average price" needs the average first, then a comparison. Subqueries and CTEs let you build an answer in steps.
The examples use a fictional online bookstore, PageTurn, with tables books (book_id, title, genre, price) and sales (sale_id, book_id, sale_date, qty).
Scalar subquery: one value
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);The inner query returns a single number, and the outer query compares against it.
IN and EXISTS: a list or a check
Books that have sold at least once:
SELECT title FROM books
WHERE book_id IN (SELECT book_id FROM sales);The same with EXISTS:
SELECT b.title FROM books b
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.book_id = b.book_id);EXISTS stops as soon as it finds a match. For "never sold", prefer NOT EXISTS over NOT IN: if the subquery returns any NULL, NOT IN returns no rows at all.
Correlated subquery: depends on the outer row
Books priced above the average of their own genre:
SELECT title, genre, price
FROM books b
WHERE price > (SELECT AVG(price) FROM books WHERE genre = b.genre);The inner query refers to b.genre, so it is evaluated for each outer row. It is readable, but a window function (AVG(price) OVER (PARTITION BY genre)) is often clearer and faster.
Derived table: a subquery in FROM
SELECT genre, AVG(units) AS avg_units_per_book
FROM (
SELECT b.genre, b.book_id, SUM(s.qty) AS units
FROM books b JOIN sales s ON s.book_id = b.book_id
GROUP BY b.genre, b.book_id
) per_book
GROUP BY genre;This is an aggregate of an aggregate: total units per book, then the average per genre.
CTE: the same idea, named and readable
WITH per_book AS (
SELECT b.genre, b.book_id, SUM(s.qty) AS units
FROM books b JOIN sales s ON s.book_id = b.book_id
GROUP BY b.genre, b.book_id
)
SELECT genre, AVG(units) AS avg_units_per_book
FROM per_book
GROUP BY genre;A CTE (Common Table Expression) gives the intermediate result a name at the top. You can chain several:
WITH monthly AS (...),
ranked AS (SELECT *, RANK() OVER (...) AS r FROM monthly)
SELECT * FROM ranked WHERE r <= 3;Which should you use?
| Situation | Good choice |
|---|---|
| Compare with one overall value | Scalar subquery |
| "Has / has not" checks | EXISTS / NOT EXISTS |
| Several steps, or a step reused | CTE |
| Filter on a window function | CTE (or derived table) |
| Comparison within a group | Window function |
In interviews, CTEs are usually the best choice for anything with more than one step: they show your thinking in order and are easy to debug, because you can run each step separately.
Recursive CTEs (a preview)
CTEs can also be recursive, which is how you walk hierarchies such as an employee-manager chain or a category tree. You will rarely need this as a fresher, but it is good to know it exists.
Practise
Work through subqueries and then CTEs. Try rewriting a few subquery answers as CTEs; it is the fastest way to become fluent in both.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: