Level 14: Queries inside queries: subqueries

Lesson

Sometimes a question contains another one. “Which movies have a rating above the average?” first requires computing the average, then comparing each movie with it. A subquery answers the first question inside the second: it is a complete SELECT written between parentheses inside another query.

Why not write WHERE rating > AVG(rating)? Because WHERE works row by row, before any aggregate is calculated. The subquery (SELECT AVG(rating) FROM movies) is an independent query, calculated separately: its value (7.2) is then used like a plain number.

A scalar subquery returns a single value, one row and one column: you compare it with =, < or >. It really must return a single row: in standard SQL, several rows cause an error, and SQLite silently takes the first one.

A subquery that returns a column of several values is used with IN: WHERE author_id IN (SELECT id FROM authors WHERE country = 'Sweden'). NOT IN calls for caution: if the subquery returns a single NULL, NOT IN no longer returns any row (see level 12). Add WHERE … IS NOT NULL in the subquery, or use NOT EXISTS (level 15).

Finally, a subquery can replace a table in FROM: you then query its result like a temporary table, which must be given an alias. FROM (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) AS t lets you, for example, compute the average of the totals per seller.

Reading tip: always start with the subquery. Run it on its own to see what it returns, then read the main query.

Syntax

SELECT …
FROM t
WHERE x > (SELECT AVG(x) FROM t) AND id IN (SELECT t_id FROM u);

Worked example

SELECT title, rating
FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

The subquery computes the average rating (7.2); the main query keeps the movies above it.

Example result

Key points

• The subquery goes between parentheses.
• Scalar: one value, compared with = < >. Column: used with IN.
• In FROM, a subquery behaves like a temporary table.

Common pitfalls

• WHERE salary > AVG(salary) is not allowed: you need a subquery.
• NOT IN with a subquery that contains a NULL returns nothing.

Going further

Standard SQL also offers x > ALL (subquery) (“greater than all the values”) and x > ANY (subquery) (“greater than at least one”). SQLite does not know them: you write x > (SELECT MAX(…) …) and x > (SELECT MIN(…) …). One detail differs: on an empty subquery, > ALL is true, whereas the comparison with the maximum gives NULL.

IN or EXISTS? To keep the rows that have a match, both give the same result. For the opposite, prefer NOT EXISTS to NOT IN as soon as the column can contain NULL (exercise 14.4).

The level’s 5 exercises

1. Guided · Show the name and salary of the employees who earn more than the average salary. (Table: staff)
2. Practice · Show the title of the books written by Swedish or German authors. (Tables: books, authors)
3. Practice · Show the id, type and price of the most expensive room. (Table: rooms)
4. Practice · Show the name of the directors who have directed no movie. Careful: one movie of the table has no known director. (Tables: directors, movies)
5. Challenge · What is the average of the sales totals per seller? (First compute each seller's total, then the average of those totals.) (Table: sales)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic