Topic 21 of 52 · Intermediate
Subqueries & EXISTS
Queries inside queries: scalar, IN, EXISTS and correlated.
Concept
A subquery is a SELECT nested inside another statement. A scalar subquery returns one value; others return a list or a table.
A correlated subquery references the outer row and runs once per outer row — powerful but can be slow.
EXISTS stops at the first match and handles NULLs safely. NOT IN breaks if the subquery returns any NULL, so prefer NOT EXISTS.
Code (SQL · MySQL 8)
-- Above-average orders
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
-- Highest paid per department (correlated)
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.dept_id = e.dept_id);
-- Customers who ordered
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Remember
- Prefer NOT EXISTS over NOT IN.
- Correlated subqueries can often be rewritten as JOINs or windows.
- Derived tables (subquery in FROM) need an alias.
Interview questions
Find the second-highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use DENSE_RANK() = 2.
Why is NOT IN dangerous?
If the subquery contains a NULL, x NOT IN (...) is UNKNOWN for every row, so nothing is returned.
Lesson test
Take an 8-question test on “Subqueries & EXISTS” — concepts, output prediction, bug spotting and interview questions.