Learn SQL: EXISTS

Does it exist? EXISTS and the NOT IN trap

"Which members have never brought in anyone else?" Sanne wants to send them a leaflet. You write a tidy query with `NOT IN`, and get... nothing. Behind that hides one of the nastiest traps in SQL.

NOT IN, the opposite

NOT IN with a subquery keeps the rows that are not in the list. This query finds the books that have never been loaned.

That works fine, as long as there's no NULL in the list.

SELECT title FROM books WHERE id NOT IN (SELECT book_id FROM loans)

Result
title
De vergeten toren
Fietsen door Europa
Brieven aan niemand

The trap, NOT IN with NULL

referrer_id is the member who brought someone in, and it's often NULL. If you use NOT IN to find who never brought anyone in, you get no rows at all. Below we count them: 0, even though there are really seven.

Why? 5 NOT IN (1, 2, NULL) means 5 <> 1 AND 5 <> 2 AND 5 <> NULL, and a comparison with NULL is never true. So take the NULLs out of the list, or use NOT EXISTS.

SELECT COUNT(*) AS found FROM members WHERE id NOT IN (SELECT referrer_id FROM members)

Result
found
0

SELECT id, name, referrer_id FROM members

members
id	name	referrer_id
1	Sanne Willems	NULL
2	Jef Wouters	1
3	Amira El Idrissi	1
4	Pieter Desmet	NULL
5	Lotte Vandamme	4
6	Bert Claeys	NULL
7	Nina De Clercq	3
8	Hamid Benali	2

EXISTS checks whether there's anything

EXISTS (subquery) is true if the subquery returns at least one row. What exactly it selects doesn't matter, which is why you often write SELECT 1.

Here the subquery refers to the outer row (m.id): for each member it checks whether there's an open loan. A subquery like that is called *correlated*. NOT EXISTS does the opposite, and isn't bothered by NULL.

SELECT name FROM members m WHERE EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.id AND l.returned_on IS NULL)

Result
name
Sanne Willems
Nina De Clercq
Hamid Benali
Wout Lambrecht
Jonas Verhaeghe

Try it yourself

Which members have never borrowed anything? Show their name.

Ready for more?

The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson