A subquery nests SELECT inside another statement—in WHERE, FROM, or SELECT list. They express multi-step logic; CTEs (next module) often improve readability of the same logic.
Subquery in WHERE
SELECT name, email
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE total > 100
);Practice: Seed customers and orders sample data, then run each query. Verify row counts manually.
Scalar subquery
SELECT c.name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.id) AS order_count
FROM customers c;Correlated subqueries reference outer rows—powerful but can be slow; JOIN + GROUP BY is often faster.
EXISTS
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);EXISTS stops at first match—efficient for “has related rows” checks.
Important interview questions and answers
- Q: IN vs EXISTS?
A: Semantically similar for many cases; EXISTS often better when subquery is large and you only need presence. - Q: Subquery in FROM?
A: Derived table / inline view—must have alias in most engines.
Self-check
- Which operator tests for related rows without returning data?
- When might JOIN beat correlated subquery?
Tip: If a correlated subquery feels slow, try JOIN + GROUP BY or a CTE.
Interview prep
- EXISTS vs IN?
EXISTS stops at first match; often efficient for presence checks.
- Correlated subquery?
Inner query references outer row—can be expensive.