Lesson 17 of 26 · see the practice database

Subqueries

A subquery is a query written inside another query, in brackets. The inner query runs and hands its result to the outer one. It lets you answer in one go a question that would otherwise take two steps: first find the average price, then find the products above it.

A subquery that returns one value

Lesson 11 showed that WHERE price > AVG(price) is not allowed. A subquery supplies the average as a plain number.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price;

The inner query returns one row with one column. Such a subquery can stand anywhere a single value can. You can run the inner part alone to see what it gives, which is the first step in understanding any query that contains one.

Who has the highest salary?

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

The second highest salary, a favourite interview question, is the highest among the salaries below the highest.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

A subquery that returns a list: IN

When the inner query returns one column with several rows, use IN.

SELECT name, city
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');

The inner query makes a list of customer numbers with a cancelled order. The outer query shows the customers whose id is in that list. A customer is shown once even if they cancelled several times, which a join would not do without DISTINCT.

Products that appear in order 7:

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 7);

NOT IN, and its trap

NOT IN finds the rows that are missing from the list. These are the customers who have never ordered.

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Now the trap promised in the lesson on NULL. If the list contains even one NULL, NOT IN returns nothing at all. Try to find the employees who are not anybody's manager:

SELECT name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

No rows, though several employees manage nobody. The list of manager numbers includes the NULL of the person at the top, and "is not equal to unknown" is never true. Remove the NULLs inside the subquery, or use NOT EXISTS, below.

SELECT name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

EXISTS

EXISTS asks only one thing of a subquery: does it return any row at all? It is true or false.

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Look at the inner query. It mentions c.id, a column of the outer query. So it cannot run by itself. The database works through the customers one by one, and for each asks, "is there any order with this customer's number?" A subquery that refers to the outer query is called a correlated subquery.

What the inner SELECT lists does not matter to EXISTS, so people write SELECT 1.

NOT EXISTS is the safe way to find what is missing. It has no trouble with NULL.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT e.name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id = e.id);

A subquery in the SELECT list

A subquery that returns one value can be a column. It is worked out for each row of the outer query.

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY orders DESC, c.name;

Customers with no orders get 0, with no left join needed. It is neat for one extra figure. For several figures from the same table, a join with GROUP BY is clearer and usually faster.

A subquery also lets you compare each row with a total.

SELECT name, price,
       ROUND(100.0 * price / (SELECT SUM(price) FROM products), 1) AS percent_of_all
FROM products
ORDER BY price DESC
LIMIT 4;

Comparing with the row's own group

A correlated subquery can compare each row with a figure for its own group. These are the products that cost more than the average of their own category.

SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (SELECT AVG(x.price) FROM products x WHERE x.category = p.category)
ORDER BY p.category, p.price;

The same table is used twice, with two aliases, as in a self join. The highest-paid person in each department works the same way.

SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary) FROM employees x WHERE x.department = e.department)
ORDER BY e.department;

A subquery in FROM

The result of a query is a table, so it can be used as one. Put the subquery in the FROM and give it an alias. This is how you summarise a summary. First count the orders for each customer, then average those counts.

SELECT ROUND(AVG(n), 2) AS average_orders_per_customer
FROM (
  SELECT customer_id, COUNT(*) AS n
  FROM orders
  GROUP BY customer_id
) AS per_customer;

A subquery in FROM is often called a derived table. Most databases insist on the alias. The lesson on views and WITH shows a tidier way to write the same thing.

Subquery or join?

Many questions can be answered either way.

• Use a join when you need to show columns from both tables.
• Use IN or EXISTS when the second table only decides which rows to keep.
• Use NOT EXISTS to find what is missing.
• If a subquery makes the query read like the question in plain words, that is a good reason to use it.

Modern databases often run the two forms equally fast. Choose the one that is easier to read and to get right.

Rules to remember

• A subquery goes in brackets.
• After =, < or >, it must return one row and one column. If it returns several rows, most databases give an error. SQLite quietly uses the first row, which can hide a mistake.
• After IN, it must return one column.
• Run the inner query on its own first, whenever it can run on its own.

Try it yourself

SELECT name, salary FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);

SELECT name FROM products WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 20);

SELECT name FROM products WHERE price = (SELECT MIN(price) FROM products);

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.