Lesson 5 of 14 · 7 min · Summarising data
Subqueries: a query inside a query
What you will learn
- Subqueries that return one value, a list or a table
INandEXISTS- Correlated subqueries
- A subquery in
FROM(a derived table) - When a join or CTE is the clearer choice
In simple words
Sometimes one question depends on the answer to another. "Which orders are bigger than the average order?" First you need the average, then you compare. A subquery lets you write the first question inside the second, in brackets, so the database works it out and uses the answer.
Words to know
- Subquery: a
SELECTwritten inside another statement, in brackets. - Scalar subquery: one that returns a single value.
- Correlated subquery: one that refers to a column of the outer query and runs once per outer row (logically).
- Derived table: a subquery in
FROMthat acts like a temporary table.
A subquery that returns one value
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
The average of the six orders is 916.67, so this returns 101 (1200.00) and 103 (1500.00). Order 105 (900.00) is just below the average and is not returned. If a scalar subquery returns more than one row, the database raises an error.
A subquery that returns a list: IN
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'paid');
returns Asha and Meena, the customers with at least one paid order. NOT IN gives the opposite, but remember the NULL trap from lesson 2: prefer NOT EXISTS.
EXISTS
EXISTS asks "is there at least one matching row?" and returns true or false. The subquery's selected columns do not matter; SELECT 1 is the convention.
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id AND o.status = 'paid');
The inner query refers to c.customer_id from the outer query, which makes it correlated. EXISTS stops at the first match, handles NULLs safely, and is the right tool for "has any" and (with NOT) "has none" questions.
A subquery in FROM
A subquery can be used like a table if you give it an alias:
SELECT t.customer_id, t.total
FROM (SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id) AS t
WHERE t.total > 1500;
Totals are Asha 2500, Ravi 1500 and Meena 1500, so only customer 1 passes. This is how you filter on an aggregate and then do more work on it. MySQL and PostgreSQL both require the alias on a derived table.
Join, subquery or CTE?
| Situation | Reach for |
|---|---|
| Need columns from both tables | join |
| Only checking whether a match exists | EXISTS |
| One computed value to compare against | scalar subquery |
| Several steps, each building on the last | CTE (a later lesson) |
Do not worry about "which is faster" first. Write the clearest version, then look at the query plan if it is slow (index lesson). Modern optimisers often turn equivalent forms into the same plan.
Common mistakes
- A scalar subquery that returns several rows (error), such as comparing to
(SELECT amount FROM orders). NOT INwith a nullable column in the subquery.- Forgetting the alias on a derived table.
- Deeply nested subqueries that nobody can read; switch to a CTE.
- Correlated subqueries on very large tables without checking the plan.
Try it yourself
- Find orders larger than the average order amount.
- Find customers who have at least one pending order, using
INand again usingEXISTS. - Find customers with no orders using
NOT EXISTS. - Show each customer with the number of their orders using a correlated subquery, then rewrite it with a join and
GROUP BY. - In a derived table, compute totals per customer and keep those above the overall average total.
Check yourself
What is the difference between IN and EXISTS?
IN compares a value to a list the subquery returns. EXISTS only checks whether the subquery returns any row, and it handles NULLs safely.
What is a correlated subquery?
A subquery that uses a column from the outer query, so its result depends on the current outer row.
What happens if a scalar subquery returns two rows?
The database raises an error, because a single value was expected.
Why does a subquery in FROM need an alias?
It is treated as a table, and every table in a query needs a name.
Interview questions
Find the second highest salary.
One way: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);. A more general way uses DENSE_RANK() (window functions lesson) so you can pick the Nth highest.
When would you choose EXISTS over a join?
When you only need to know whether a related row exists and do not need its columns. It avoids duplicate rows from one-to-many matches.
What is the difference between a subquery and a join in performance?
Often none, because the optimiser rewrites them. Correlated subqueries can be slow on large data; check the plan and consider a join or window function.
Summary
- A subquery is a
SELECTin brackets; it can give one value, a list or a table. INcompares to a list;EXISTSchecks for any match and is NULL-safe.- Correlated subqueries use outer columns; derived tables need an alias.
- Use a join or CTE when a query becomes hard to read.
Going deeper
- Subquery expressions:
EXISTS,IN,ANYandALL. - Subqueries in the FROM clause: derived tables.
- MySQL subqueries: the MySQL reference.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.