After this lesson you can put one query inside another to answer questions that need two steps, such as "who earns more than the average?", and you can avoid a well-known trap that makes a query return nothing.
A subquery (or inner query) is a SELECT written in parentheses inside another statement. The database evaluates it and uses its result in the outer query. Where you write it decides what shape it must return.
Scalar subquery: one value
A scalar subquery returns a single value, one row and one column, so it can be used anywhere a number or a piece of text could. Which employees earn more than the company average?
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Meera | Iyer | 180000 |
| Vikram | Singh | 150000 |
| Arjun | Rao | 125000 |
| Kavya | Nair | 125000 |
| Lata | Bose | 115000 |
| Dev | Patel | 110000 |
| Ravi | Pillai | 95000 |
+------------+-----------+--------+The inner query computes the average salary (90,050 in the sample data), and the outer query compares each salary to it. Without a subquery you would run the average first, copy the number, and paste it into a second query, which breaks as soon as the data changes.
If a scalar subquery returns more than one row, the database raises an error. If it returns no rows, the value is NULL.
You can also use a scalar subquery in the SELECT list, to put a value next to every row:
SELECT first_name, salary,
salary - (SELECT AVG(salary) FROM employees) AS difference_from_average
FROM employees
WHERE id <= 4;+------------+--------+-------------------------+
| first_name | salary | difference_from_average |
+------------+--------+-------------------------+
| Meera | 180000 | 89950.0 |
| Arjun | 125000 | 34950.0 |
| Kavya | 125000 | 34950.0 |
| Rohan | 82000 | -8050.0 |
+------------+--------+-------------------------+The differences show a decimal point because the average is not a whole number. The inner query does not depend on the outer row, so the database can compute it once. The next lesson covers subqueries that do.
List subquery: IN
A subquery that returns one column and many rows is a list. Use it with IN:
SELECT first_name, last_name, job_title
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE city = 'Pune')
ORDER BY id;+------------+-----------+----------------------+
| first_name | last_name | job_title |
+------------+-----------+----------------------+
| Meera | Iyer | Engineering Director |
| Arjun | Rao | Senior Developer |
| Kavya | Nair | Senior Developer |
| Rohan | Das | Developer |
| Isha | Kapoor | Developer |
| Pooja | Reddy | Support Lead |
| Karan | Mehta | Support Engineer |
| Sana | Qureshi | Support Engineer |
| Diya | Chopra | Junior Developer |
+------------+-----------+----------------------+The inner query finds the departments in Pune: Engineering and Support. The outer query keeps employees whose department_id is in that list. You could also do this with a join, and the choice is mostly about readability. With IN it is clear that you only want employees and do not need any department columns.
Table subquery: FROM
A subquery in FROM produces a temporary table, called a derived table. It must have an alias. This lets you aggregate first and then work on the aggregated rows, which is otherwise hard:
SELECT d.name AS department, s.average_salary
FROM departments d
INNER JOIN (
SELECT department_id, ROUND(AVG(salary), 0) AS average_salary
FROM employees
GROUP BY department_id
) AS s ON s.department_id = d.id
WHERE s.average_salary > 80000
ORDER BY s.average_salary DESC;+-------------+----------------+
| department | average_salary |
+-------------+----------------+
| Engineering | 108333.0 |
| Finance | 92500.0 |
| Sales | 86400.0 |
| Marketing | 80667.0 |
+-------------+----------------+The inner query makes one row per department with its average salary. The outer query joins that to departments for the names and keeps the departments averaging over 80000. In the lesson on CTEs you will see a tidier way to write the same shape.
Comparing with ALL or ANY
Some databases let a subquery follow a comparison operator with ANY or ALL. For example, "paid more than everyone in Marketing":
SELECT first_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 3)
ORDER BY salary DESC;> ALL means greater than every value in the list, that is, greater than the maximum. SQLite, which this course uses to check its output, does not support ANY and ALL with a subquery, so the block above is shown without output. Writing the maximum explicitly says the same thing and runs everywhere:
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 3)
ORDER BY salary DESC;+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
| Vikram | 150000 |
| Arjun | 125000 |
| Kavya | 125000 |
| Lata | 115000 |
+------------+--------+The NOT IN trap
This is the most important thing in the lesson. Find the departments that have no employees. The obvious answer is NOT IN:
SELECT id, name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees);(no rows)It returns nothing, although Research has no employees. The reason is NULL. Employee 18 has NULL as department_id, so the inner list is 1, 2, 3, 4, 5, NULL. For Research (id 6), 6 NOT IN (1, 2, 3, 4, 5, NULL) means "6 differs from each of them", and the comparison with NULL is neither true nor false but unknown. An unknown anywhere in the chain makes the whole test unknown, and WHERE keeps only true rows.
Two safe fixes. Remove the NULLs from the list:
SELECT id, name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL);+----+----------+
| id | name |
+----+----------+
| 6 | Research |
+----+----------+Or use NOT EXISTS, which has no such problem and is the subject of its own lesson. The general rule: whenever the list comes from a column that might contain NULL, do not use NOT IN without filtering. IN itself is unaffected, because a match on a real value is still a true.
Subquery or join?
Many subqueries can be rewritten as joins, and the other way round. A rough guide:
- Use a join when you need columns from both tables in the result.
- Use a subquery when you only need to filter on the other table, or when the inner step is an aggregate such as an average.
- Prefer the form that is clearest to read. Database engines are often good at turning either form into the same plan, but do not assume it; test on your own data if speed matters.
Differences between databases
Scalar, IN and FROM subqueries work in MySQL, PostgreSQL, SQLite and SQL Server. ANY and ALL with a subquery work in MySQL, PostgreSQL and SQL Server, but not in SQLite. MySQL and SQL Server require an alias on a subquery in FROM. For MySQL specifics, see Subqueries in MySQL.
Common mistakes
- Returning too many rows from a scalar subquery. Using
=with a subquery that may return several rows causes an error. - Using NOT IN on a column with NULLs. It silently returns nothing. Filter with
IS NOT NULLor useNOT EXISTS. - Forgetting the alias on a derived table. Name it and refer to its columns through that name.
- Selecting two columns in an IN subquery. The list must have exactly one column.
- Using a subquery where a join would be clearer. If you need columns from both tables, join.
Exercise
Using plain subqueries, show the projects whose budget is above the average project budget, and the names of employees who work on project 2 (use an IN subquery on assignments).
Show one possible answer
SELECT name, budget
FROM projects
WHERE budget > (SELECT AVG(budget) FROM projects)
ORDER BY budget DESC;
SELECT first_name, last_name
FROM employees
WHERE id IN (SELECT employee_id FROM assignments WHERE project_id = 2)
ORDER BY id;+-----------------+--------+
| name | budget |
+-----------------+--------+
| Mobile App | 650000 |
| Customer Portal | 400000 |
+-----------------+--------+
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Arjun | Rao |
| Kavya | Nair |
| Isha | Kapoor |
| Diya | Chopra |
+------------+-----------+Key points
- A subquery is a SELECT inside another query, written in parentheses. It runs first and its result is used by the outer query.
- A scalar subquery returns one value, a list subquery returns one column of values for IN, and a table subquery goes in FROM and needs an alias.
- Subqueries answer questions that need an intermediate result, such as which employees earn more than the company average.
- NOT IN returns no rows at all if the subquery produces a NULL. Filter the NULLs out, or use NOT EXISTS.
- A join is often an alternative to a subquery. Choose whichever reads more clearly.