Subqueries

You will be able to put one SELECT inside another and know what shape it must return: one value, a set of values, a yes-or-no, or a table. In Fundamentals a WHERE could not call AVG(), and a comparison with NULL never matched; the query-inside-a-query answers the first, and the NULL trap comes back inside NOT IN.

The examples use a small library. books(title, genre, pages) holds Tide, Ash and Moth (fantasy, 320 / 280 / 240 pages) and Orbit and Vega (scifi, 500 / 300). members has Mara (1), Noor (2), Omar (3), Pia (4). loans(member_id, title) has rows for Mara twice, Omar once, and one desk loan with a NULL member_id.

What the subquery returns decides where it fits

A scalar subquery returns one row and one column and works anywhere a value does. It runs first and the outer query uses its answer:

SELECT title FROM books WHERE pages > (SELECT AVG(pages) FROM books);

Orbit

The inner query yields 328.0, so the outer one is pages > 328.0. SQLite does not check that a scalar subquery is really one row. Give it several and it silently uses the first; give it none and you get NULL:

SELECT (SELECT pages FROM books WHERE genre = 'scifi');   -- 500, and Vega's 300 is ignored
SELECT (SELECT pages FROM books WHERE genre = 'poetry');  -- NULL, printed as an empty line

An aggregate always returns exactly one row, which is why AVG and MAX are the safe scalar subqueries. Two columns where one is expected is an error.

A subquery that returns one column of many rows is a set, and IN tests membership:

SELECT name FROM members WHERE id IN (SELECT member_id FROM loans);

Mara
Omar

Pairs work too: WHERE (genre, pages) IN (SELECT genre, MAX(pages) FROM books GROUP BY genre) keeps the longest book of each genre, Tide and Orbit.

EXISTS asks only whether the inner query returns any row at all. What it selects is irrelevant, by convention 1. It refers to the outer row, so it is a correlated subquery:

SELECT name FROM members m
WHERE EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.id);

It gives the same Mara and Omar. A join would print Mara twice, once per loan, because it multiplies rows where EXISTS only checks.

A subquery in FROM is a derived table: the outer query reads its result as if it were a stored table, so you can filter on a grouped result.

SELECT genre, avg_pages
FROM (SELECT genre, AVG(pages) AS avg_pages FROM books GROUP BY genre) AS g
WHERE avg_pages > 300;

scifi|400.0

Correlated subqueries

When the inner query mentions a column of the outer row, it cannot run once. It runs again for every outer row with that row's values:

SELECT title, genre FROM books b
WHERE pages > (SELECT AVG(pages) FROM books WHERE genre = b.genre);

Tide|fantasy
Orbit|scifi

For Tide the inner query averages the fantasy books (320, 280, 240) to 280.0, and 320 beats it. For Vega the average is 400.0 and 300 does not. Only a correlated query can compare each row with its own group's figure. Qualify with an alias (b.genre) to say which row you mean.

Common mistakes

• NOT IN over a set containing NULL. WHERE id NOT IN (SELECT member_id FROM loans) prints nothing, though Noor and Pia never borrowed: for Noor, 2 <> NULL is unknown, so no row can be proved true. NOT EXISTS, or adding WHERE member_id IS NOT NULL inside, prints Noor and Pia.
• An unqualified column in the inner query. WHERE genre = genre compares the inner table's column with itself, so it is always true and the average becomes the whole table's. The query above then prints only Orbit, with no error.
• Trusting a scalar subquery to be one row. An inner WHERE that matches several rows is not rejected; you get whichever row comes first, and the result changes quietly when the data does.
• Settling ties and gaps by accident. A value equal to its group average is not above it, so >= over-reports. AVG skips NULLs, so a NULL value is neither above nor below anything; wrapping it in COALESCE(x, 0) pulls the average down and changes who qualifies.

Your exercise

Print each employee paid strictly more than the average of their own department, beside that average. The starter creates the table and prints every employee; the rows are chosen so that comparing with the company-wide average, using >=, or counting NULL as 0 each print different lines.