Scalar subqueries and subqueries in WHERE

What is a subquery?

A subquery (or nested query) is a SELECT query written inside another query, between parentheses. The inner one runs first and its result feeds the outer one. It is the natural way to answer questions that depend on a value that must be computed first.

Scalar subquery

A scalar subquery returns a single value (one row and one column). That is why you can use it anywhere a value fits, such as the right-hand side of a comparison:

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

First AVG(price) is computed (a number), and then the rows are compared against it. If the subquery returned more than one row here, SQLite would error: a comparison with > expects a single value.

Mental trick: read the subquery as if it were a constant you don't yet know. "Products more expensive than the average".

Subqueries with IN

When the subquery returns a column with several values, you can't use =, but you can use IN, which checks membership in a set:

SELECT name
FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE name IN ('peripherals', 'displays')
)
ORDER BY name;

The subquery produces the list of id of those categories; the outer one keeps the products whose category_id is in that list. Its opposite is NOT IN.

Beware of NULL: if the subquery of a NOT IN can return NULL, the result becomes unpredictable. In those cases NOT EXISTS is usually preferred (next lesson).

Examples

Products above the average price

-- Mentally replace the subquery with its value (the average):
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

IN with a list computed by another query

SELECT name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE name = 'computers')
ORDER BY name;