Practical 7: Subqueries with EXISTS

Why NOT EXISTS is always right

[Practical 7: Subqueries with IN] showed NOT IN returning nothing at all because a NULL had got into the list. The same data, asked with NOT EXISTS:

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 b1.title
FROM book b1
WHERE b1.author_id NOT IN (SELECT b2.author_id FROM book b2
                           WHERE b2.subject = 'Programming');

Nothing, as before. Now with NOT EXISTS:

SELECT b1.title
FROM book b1
WHERE NOT EXISTS (SELECT 1 FROM book b2
                  WHERE b2.subject = 'Programming'
                    AND b2.author_id = b1.author_id)
ORDER BY b1.book_id;

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

The real answer.

The reason is that EXISTS never compares anything with the list. It asks whether a row is there, and a row either is or is not; there is no third possibility for a NULL to occupy. NOT IN has to compare, and a comparison with NULL is neither true nor false.

When a subquery's column can be NULL, use NOT EXISTS. That is the practical's main finding.

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

EXISTS against IN, side by side

A question IN cannot ask

IN compares one value. When the match needs two columns at once, EXISTS is the natural way.

SELECT m.member_name, b.title
FROM member m JOIN book b ON 1 = 1
WHERE EXISTS (SELECT 1 FROM loan l
              WHERE l.member_id = m.member_id
                AND l.book_id   = b.book_id
                AND l.returned_on IS NULL)
ORDER BY m.member_id;

+---------------+-------------------+
| member_name   | title             |
+---------------+-------------------+
| Ravi Deshmukh | Data Structures   |
| Neha Patil    | Computer Networks |
+---------------+-------------------+

The member and book pairs where that member currently holds that book. Two columns have to agree at once, and EXISTS says so in two lines. MySQL does have a row constructor, WHERE (m.member_id, b.book_id) IN (SELECT ...), which can do it too, and it is far less readable.

The hardest one: for all

"Which members have borrowed every Databases book?" cannot be asked directly, because SQL has no "for all". It is asked by turning it inside out: a member for whom there is no Databases book that they have not borrowed.

Two NOT EXISTS, one inside the other:

SELECT m.member_name
FROM member m
WHERE NOT EXISTS (
    SELECT 1 FROM book b
    WHERE b.subject = 'Databases'
      AND NOT EXISTS (
          SELECT 1 FROM loan l
          WHERE l.book_id = b.book_id AND l.member_id = m.member_id
      )
)
ORDER BY m.member_id;

Nobody, in this library, because no member has taken out all three Databases books.

Read it from the inside: the innermost query asks "did this member borrow this book?". The middle NOT EXISTS asks "is there a Databases book this member did not borrow?". The outer NOT EXISTS keeps the members for which there is no such book.

This is called relational division, it is the standard hard question of a database paper, and it is worth writing into your journal once even though it will not be needed often. Check it by making it true:

INSERT INTO loan VALUES (8, 2, 4, '2025-08-01', NULL),
                        (9, 3, 4, '2025-08-01', NULL),
                        (10, 6, 4, '2025-08-01', NULL);

SELECT m.member_name
FROM member m
WHERE NOT EXISTS (
    SELECT 1 FROM book b
    WHERE b.subject = 'Databases'
      AND NOT EXISTS (
          SELECT 1 FROM loan l
          WHERE l.book_id = b.book_id AND l.member_id = m.member_id
      )
)
ORDER BY m.member_id;

+--------------+
| member_name  |
+--------------+
| Imran Shaikh |
+--------------+

Imran Shaikh has now borrowed all three, and the query found him. A query that returns nothing has not been tested. Make it true, run it again, and you know it works.

What beginners get wrong

Writing an uncorrelated EXISTS. WHERE EXISTS (SELECT 1 FROM loan) is true for every row, because there is always at least one loan. The inner query must mention the outer one.

Selecting columns inside EXISTS and thinking they matter. They do not; only the existence of rows does.

Using NOT IN where the column can be NULL. Silently empty.

Trying to test two columns with IN. Use EXISTS.

Expecting a "for all" keyword. There is none; use two NOT EXISTS.

Not testing a query that returns nothing. Insert the data that should make it true and run it again.

Quick revision

• EXISTS (subquery) is true when the subquery returns at least one row.
• What the subquery selects does not matter; write SELECT 1.
• An EXISTS is almost always correlated: the inner query mentions an outer column.
• A correlated subquery cannot be run on its own and conceptually runs once per outer row.
• NOT EXISTS is unaffected by NULLs, where NOT IN returns nothing.
• Unmatched rows can be found three ways: outer join, NOT IN, NOT EXISTS.
• "For all" is written as two nested NOT EXISTS, and is called relational division.
• Test a query that returns nothing by making it true.

What goes in your journal

Aim, then EXISTS and NOT EXISTS over the same pair of tables with their results, then the same question asked with NOT IN so that the two can be compared.

Include the NULL demonstration: the NOT IN that returns nothing and the NOT EXISTS that returns the right answer, on the same data. One line under them saying why is the whole practical.

If you have room, the two nested NOT EXISTS, with the run that returns nothing and the run after the extra loans that returns a name. The pair proves the query works, where either alone proves very little.

Test yourself

1. When is EXISTS true? When its subquery returns at least one row, whatever that row contains.

2. What is a correlated subquery? One that refers to a column of the outer query, so it cannot be run on its own and conceptually runs once for each row of the outer query.

3. Why is NOT EXISTS safe where NOT IN is not? Because EXISTS only asks whether rows are there and never compares a value with NULL, while NOT IN compares and a comparison with NULL is never true.

4. Does it matter what the subquery inside EXISTS selects? No. SELECT 1 is the convention precisely because the columns are ignored.

5. How is "every" expressed in SQL? As a double negative: there is no member of the set for which some required thing does not exist. Two nested NOT EXISTS.

6. Your query returned no rows. How do you know it is right? Insert data that should make it return something, and run it again. A query that has only ever returned nothing has not been tested.