Subqueries

“Which products cost more than the average?” This question has two steps: first find the average price (293.56), then compare with it. You could type the number into the query by hand, but tomorrow, when prices change, the query would be out of date. A better way is to do both steps in one query. For that we use a subquery.

A subquery in WHERE

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

name | price
Laptop | 1450
Smartphone | 899.99
Monitor | 310

The inner query runs first and returns one number; the outer query then uses it like an ordinary number. Such a subquery is called scalar: it must return exactly one row and one column. This is also the portable answer to the earlier question “what is the name of the most expensive product?”:

SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

name | price
Laptop | 1450

Subqueries with IN and NOT IN

If a subquery returns many rows in one column, it can serve as the list for IN. Let's find the students enrolled in physics courses. Subqueries can be nested too: the innermost one returns the ids of physics courses, the middle one the ids of students enrolled in them.

SELECT first_name, last_name
FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id IN (SELECT id FROM courses WHERE subject = 'Physics')
)
ORDER BY id;

first_name | last_name
Murad | Əliyev
Elvin | Quliyev
Rəşad | Kərimov
Fidan | Cəfərova

SELECT name, category
FROM products
WHERE id NOT IN (SELECT product_id FROM orders)
ORDER BY id;

name | category
Monitor | Electronics

SELECT COUNT(*) AS found
FROM products
WHERE id NOT IN (1, 2, NULL);

found
0

EXISTS and correlated subqueries

A correlated subquery refers to a column of the outer query, for example o.customer_id = c.id. Logically it is recalculated for every row of the outer query. EXISTS (...) is true when the subquery returns at least one row — what the rows contain doesn't matter, so people usually write SELECT 1 inside.

SELECT c.name, c.country
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.id;

name | country
Anar Mustafayev | Azerbaijan
Lalə Əhmədova | Azerbaijan
Emre Yılmaz | Türkiye
Zəhra Hüseynli | Azerbaijan

A correlated subquery can also sit in SELECT — then it calculates one value for each row. Below we see how many courses each 10th grader takes and their best score.

SELECT s.first_name,
       (SELECT COUNT(*)   FROM enrollments AS e WHERE e.student_id = s.id) AS courses,
       (SELECT MAX(score) FROM enrollments AS e WHERE e.student_id = s.id) AS best
FROM students AS s
WHERE s.grade = 10
ORDER BY s.id;

first_name | courses | best
Murad | 2 | 81
Rəşad | 2 | 99
Səbinə | 2 | 88

SELECT e.student_id, e.course_id, e.score
FROM enrollments AS e
WHERE e.score > (
  SELECT AVG(e2.score)
  FROM enrollments AS e2
  WHERE e2.course_id = e.course_id
)
ORDER BY e.course_id, e.score DESC;

student_id | course_id | score
11 | 1 | 97
1 | 1 | 92
3 | 2 | 95
11 | 3 | 94
2 | 3 | 81
4 | 4 | 72
5 | 5 | 93
6 | 6 | 99
3 | 7 | 90

Subqueries in FROM and WITH

A subquery's result is a table, so it can also go in FROM, where it works like a temporary table. Most DBMSs require such a subquery to have an alias (AS t). Below we first find each course's average, then the average of those averages and the best one.

SELECT ROUND(AVG(avg_score), 1) AS avg_of_courses,
       ROUND(MAX(avg_score), 1) AS best_course
FROM (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_id
) AS t;

avg_of_courses | best_course
82 | 92.7

As subqueries pile up inside each other, a query gets hard to read. The WITH clause (a CTE — common table expression) lets you name a subquery up front and then use it like an ordinary table. WITH is supported by SQLite, PostgreSQL, SQL Server and MySQL from version 8.0.

WITH course_avg AS (
  SELECT course_id, ROUND(AVG(score), 1) AS avg_score
  FROM enrollments
  GROUP BY course_id
)
SELECT c.title, ca.avg_score
FROM course_avg AS ca
JOIN courses AS c ON c.id = ca.course_id
WHERE ca.avg_score > 85
ORDER BY ca.avg_score DESC;

title | avg_score
Python Basics | 92.7
World History | 90.5
Algebra | 87.3

SELECT first_name, age
FROM students
WHERE age > ( /* average age here */ )
ORDER BY age DESC, first_name;

first_name | age
Elvin | 17
Fidan | 17
Tural | 17
Murad | 16
Rəşad | 16
Səbinə | 16

SELECT name
FROM products
WHERE id IN (
  -- product ids from the orders of Turkish customers
)
ORDER BY name;

name
Chess set
Pen set
Smartphone