Subqueries
A subquery (inner query, nested query) is a SELECT inside another SQL statement.
Concept
A subquery (inner query, nested query) is a SELECT inside another SQL statement. The outer query uses its result.
Exactly one value (1 row × 1 column) =, >, <, in SELECT list Compare to the average Match the latest order One column, many rows IN, NOT IN, ANY, ALL, EXISTS Customers who ordered Row comparison (a, b) IN (...) Top earner per department
Re-evaluated per outer row (references outer columns) Above department average Derived table (inline view) A table in FROM Subquery inside a subquery
Subqueries can appear in SELECT, FROM, WHERE, HAVING, and in INSERT/UPDATE/DELETE statements.
Why It Matters
Subqueries let you answer multi-step questions in one statement ("customers who spent more than the average customer"). Understanding EXISTS vs IN and correlated subqueries is essential for correctness (NULL traps) and performance.
Syntax
SELECT ... FROM ... WHERE column OPERATOR (SELECT ... FROM ... WHERE ...);
SELECT ..., (SELECT ...) AS alias FROM ...;
SELECT ... FROM (SELECT ...) AS derived_alias; -- alias required (optional in PostgreSQL 16+ and Oracle)
SELECT ... FROM t WHERE EXISTS (SELECT 1 FROM u WHERE u.k = t.k);Example
14.1 Scalar subquery Employees earning more than the company average (117500):
Scalar subquery in the SELECT list:
If a scalar subquery returns more than one row, the query fails ("more than one row returned by a subquery used as an expression"). If it returns zero rows, the value is NULL.
14.2 Single-row subquery The most recent order:
14.3 Multi-row subquery with IN / NOT IN Customers who placed at least one order:
Customers with no orders:
If the subquery can return a NULL, NOT IN returns zero rows:
14.4 EXISTS and NOT EXISTS EXISTS (subquery) is TRUE if the subquery returns at least one row. The selected columns are irrelevant (SELECT 1 is conventional). EXISTS never returns UNKNOWN — only TRUE or FALSE — so it is NULL-safe.
14.5 ANY and ALL x > ANY (subquery) — TRUE if x is greater than at least one value (i.e., greater than the minimum). = ANY is the same as IN. SOME is a synonym for ANY.
x > ALL (subquery) — TRUE if x is greater than every value (i.e., greater than the maximum). <> ALL is the same as NOT IN.
Returns everyone except Priya (7 rows).
Edge cases: > ALL of an empty subquery is TRUE (vacuous truth) — every row qualifies. > ANY of an empty subquery is FALSE. SQLite does not support ANY/ALL; use MIN/MAX subqueries instead.
14.6 Multi-column subquery Highest-paid employee in each department:
Kiran (dept NULL) is missing because (NULL, 80000) IN ((NULL, 80000)) is UNKNOWN. SQL Server does not support row-value IN; use a correlated subquery, a JOIN to the aggregated derived table, or a window function (Module 17).
14.7 Correlated subquery A correlated subquery references a column from the outer query, so it is logically evaluated once per outer row.
Employees earning more than their own department's average:
(Dept 1 average 147500; dept 2 average 90000; dept 3 average 90000 so Arjun is not greater; for Kiran the subquery finds no rows with dept_id = NULL, returns NULL, and the comparison is UNKNOWN.)
Correlated subquery in SELECT — each customer's order count:
Modern optimizers often rewrite correlated subqueries into joins ("decorrelation"), but not always; on large tables, check the execution plan.
14.8 Derived tables (subquery in FROM) (Totals: 93900, 105000, 1600 → 200500 / 3.) 14.9 Nested subqueries
Products bought by customers from India:
Deep nesting becomes hard to read; CTEs (Module 16) or joins usually express this better.
14.10 Subqueries in DML 14.11 JOIN vs Subquery Returns columns from both tables Only via scalar subqueries in SELECT
Readability for "exists / not exists" questions Anti-join is less obvious EXISTS reads naturally May multiply rows (fan-out) IN/EXISTS never duplicates outer rows Usually well optimized
Modern optimizers convert many subqueries to joins (semi-joins); correlated ones may be slower Aggregates at a different grain Needs derived table Scalar subquery is natural
Rule of thumb: use a JOIN when you need columns from the other table; use EXISTS/IN when you only need to test for the existence of related rows.
14.12 EXISTS vs IN Value membership in a list/set Whether any row satisfies a condition NULL in subquery NOT IN breaks (returns no rows) NOT EXISTS is safe Stops at first match (semantically) Row-value IN not in SQL Server Any condition, any number of columns
Modern optimizers usually produce the same plan (semi-join) for IN and EXISTS
Same; NOT EXISTS often better than NOT IN
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;SELECT first_name,
salary,
salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
WHERE emp_id IN (1, 5);SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- Elena Rossi — correct here only because orders.customer_id is NOT NULL.SELECT first_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);
-- Intended: employees who manage nobody (Neha, Priya, Arjun, Kiran, Meera).
-- Actual: 0 rows, because Asha's manager_id is NULL.
-- Fix 1: exclude NULLs
SELECT first_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);
-- Fix 2 (preferred): NOT EXISTS
SELECT e.first_name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id);-- Customers with at least one delivered order
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'DELIVERED'
);-- Products never ordered
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);-- Earn more than every Sales employee (> 120000)
SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);-- Earn more than at least one Sales employee (> 60000)
SELECT first_name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 2)
ORDER BY emp_id;Expected Result
Customers whose total spend is above the average customer spend (66833.33, non-cancelled orders):
SELECT customer_id, SUM(total_amount) AS spent
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY customer_id
HAVING SUM(total_amount) > (
SELECT AVG(t.total) FROM (
SELECT SUM(total_amount) AS total FROM orders
WHERE status <> 'CANCELLED' GROUP BY customer_id
) t
)
ORDER BY spent DESC;Common Mistakes
NOT IN with a subquery that can return NULL.
Scalar subquery returning multiple rows (use an aggregate, LIMIT 1 with ORDER BY, or IN).
Forgetting the alias on a derived table (required in MySQL, SQL Server, older PostgreSQL).
Deep nesting instead of CTEs.
Correlated subqueries in SELECT for large result sets (N executions) — consider a join with GROUP BY or a window function.
Using = (subquery) when the subquery can legally return many rows.
Best Practices
Prefer EXISTS/NOT EXISTS for existence checks.
Use IN for small static lists or non-nullable subquery columns.
Replace repeated subqueries with CTEs.
Check execution plans for correlated subqueries on large tables.
Interview Relevance
Very high: "Second-highest salary", "employees earning above department average", "customers with no orders", "EXISTS vs IN", "NOT IN NULL problem", "correlated vs non-correlated subquery".
Second-highest salary (classic):
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees); -- 150000.00Practice Questions
Find the product(s) with the highest price using a subquery.
Find customers who have ordered a Laptop.
Find employees hired after their manager.
Find orders whose total is above the average order total.
Find departments whose average salary is higher than the company average.
Rewrite a NOT IN query as NOT EXISTS and as a LEFT JOIN anti-join.
Find the third-highest distinct salary using only subqueries.