Practical 7: Subqueries with IN

Debugging a subquery means running the inner query by itself. It is the first thing to do and students rarely think of it.

NOT IN

SELECT title FROM book
WHERE book_id NOT IN (SELECT book_id FROM loan)
ORDER BY book_id;

+-------------------+
| title             |
+-------------------+
| Operating Systems |
| Discrete Maths    |
+-------------------+

The two books nobody has borrowed, which is the same answer the LEFT JOIN pattern of [Practical 6: The Outer Join] gave. Both are correct and both are used; which is faster depends on the tables and the indexes.

The NULL trap, which is what this practical is really for

SELECT title FROM book
WHERE author_id NOT IN (SELECT author_id FROM book WHERE subject = 'Databases');

+-------------------+
| title             |
+-------------------+
| The C Language    |
| Data Structures   |
| Computer Networks |
| Operating Systems |
+-------------------+

Now the same question asked where the inner list contains a NULL:

INSERT INTO author (author_id, author_name, country) VALUES (6, 'Unknown', NULL);
INSERT INTO book VALUES (9, 'Mystery Book', 'Programming', 200.00, '2025-01-01', NULL);

SELECT author_id FROM book WHERE subject = 'Programming';

+-----------+
| author_id |
+-----------+
|         1 |
|         4 |
|      NULL |
+-----------+

There is a NULL in that list now. Ask which books are not by one of those authors:

SELECT title FROM book
WHERE author_id NOT IN (SELECT author_id FROM book WHERE subject = 'Programming');

Nothing at all, and there certainly are books by other authors.

Here is why, and it follows from the NULL rule of [Practical 4: Simple Queries]. x NOT IN (1, 4, NULL) means x <> 1 AND x <> 4 AND x <> NULL. That last comparison is NULL, never true, so the whole AND can never be true. It can be false, when x really is 1 or 4, and otherwise it is NULL, and a WHERE keeps only what is true. So no row survives.

Three fixes, and the first is the one to use:

SELECT title FROM book
WHERE author_id NOT IN (
    SELECT author_id FROM book
    WHERE subject = 'Programming' AND author_id IS NOT NULL
);

+------------------+
| title            |
+------------------+
| Database Systems |
| MySQL Reference  |
| Learn SQL        |
| Discrete Maths   |
+------------------+

The second is NOT EXISTS, which does not have this problem at all and is [Practical 7: Subqueries with EXISTS]. The third is the LEFT JOIN pattern.

IN with a NULL in the list is harmless; NOT IN with a NULL in the list returns nothing. That sentence is worth writing in your journal exactly as it stands.

DELETE FROM book WHERE book_id = 9;
DELETE FROM author WHERE author_id = 6;

A scalar subquery: one value

SELECT title, price FROM book
WHERE price > (SELECT AVG(price) FROM book)
ORDER BY price DESC;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| Database Systems  | 720.50 |
| Operating Systems | 655.25 |
| Data Structures   | 610.00 |
| Computer Networks | 540.75 |
+-------------------+--------+

The inner query returns one number, so it can be used anywhere a number can go. This is the query MU's examiners set most often on this practical: the rows above the average, which cannot be written without a subquery, because an aggregate is not allowed in a WHERE.

If a scalar subquery returns more than one row, the server refuses:

SELECT title FROM book WHERE price > (SELECT price FROM book);

ERROR 1242 (21000): Subquery returns more than 1 row

Error 1242. Use IN, or ANY, or add a LIMIT 1 if one row really is what you meant.

ANY and ALL

SELECT title, price FROM book
WHERE price > ALL (SELECT price FROM book WHERE subject = 'Databases')
ORDER BY price DESC;

SELECT title, price FROM book
WHERE price > ANY (SELECT price FROM book WHERE subject = 'Databases')
ORDER BY price DESC;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| Database Systems  | 720.50 |
| Operating Systems | 655.25 |
| Data Structures   | 610.00 |
| Computer Networks | 540.75 |
| MySQL Reference   | 499.00 |
| Discrete Maths    | 430.00 |
| The C Language    | 395.00 |
+-------------------+--------+

> ALL means greater than every one of them, so greater than the largest. > ANY means greater than at least one of them, so greater than the smallest. SOME is another word for ANY.

And IN is exactly = ANY. That equivalence is a fair viva question and it makes both easier to remember.

A subquery in the FROM: a derived table

SELECT subject, books
FROM (SELECT subject, COUNT(*) AS books FROM book GROUP BY subject) AS counts
WHERE books > 1;

+-------------+-------+
| subject     | books |
+-------------+-------+
| Programming |     2 |
| Databases   |     3 |
+-------------+-------+

The inner query produces a result, and the outer query treats it as a table. A derived table must be given an alias, AS counts here, or the server refuses; it has no name of its own.

This is the other way to filter on an aggregate, and it is worth comparing with the HAVING of [Practical 4: Aggregate Functions, GROUP BY and HAVING], which does the same job more directly.

Where a subquery may appear

What beginners get wrong

NOT IN against a list that may contain NULL. The answer is silently empty. Exclude the NULLs, or use NOT EXISTS.

A subquery with two columns after IN. Error 1241.

A scalar subquery that returns several rows. Error 1242.

Forgetting the alias on a derived table. The server refuses it.

Not running the inner query on its own when the answer looks wrong. It takes ten seconds and it finds most of these.

Using a subquery where a join is clearer. Both are correct; on a large table the join is often faster, and either is accepted in an examination.

Quick revision

• A subquery is a SELECT in brackets inside another statement.
• IN (subquery) needs the inner query to return exactly one column.
• NOT IN with a NULL anywhere in the list returns no rows at all.
• The fix is AND col IS NOT NULL in the subquery, or NOT EXISTS.
• A scalar subquery returns one row and one column and goes wherever a value goes.
• WHERE price > (SELECT AVG(price) FROM book) is how rows above the average are found.
• > ALL is greater than the largest; > ANY is greater than the smallest; IN is = ANY.
• A subquery in the FROM is a derived table and must be given an alias.
• Debug a subquery by running the inner query on its own.

What goes in your journal

Aim, then: IN with a typed list, IN with a subquery, the inner query run on its own so that the list it produces is visible, NOT IN, and the above-the-average scalar subquery.

Then the NULL trap, in three steps: the list with a NULL in it, the NOT IN that returns nothing, and the repaired version. Those three results together are the practical's real content, and an examiner who sees them knows you have understood rather than copied.

Test yourself

1. How many columns may the subquery after IN return? Exactly one.

2. Why does NOT IN return nothing when the list contains a NULL? Because x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and the last comparison is NULL rather than true, so the whole condition can never be true.

3. Give two ways to repair such a query. Add AND col IS NOT NULL inside the subquery, or rewrite it with NOT EXISTS.

4. What is a scalar subquery? One that returns a single row with a single column, so it can be used anywhere a single value can be used.

5. How do you find the rows whose price is above the average? WHERE price > (SELECT AVG(price) FROM book). It must be a subquery because an aggregate cannot appear in a WHERE.

6. What is the difference between > ANY and > ALL? > ANY is true if the value beats at least one of the list, so it compares with the smallest. > ALL is true only if it beats every one, so it compares with the largest.

7. What must a subquery in the FROM clause be given? An alias, because a derived table has no name of its own.