IN and NOT IN

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — A list built by a query

Take the question "which trainers have earned at least one badge?". The ids of those trainers are already in the database — in the badges table:

SELECT trainer_id FROM badges;

That is a list of ids. Put this query in parentheses after IN, and SQL uses its result as the list:

SELECT name FROM trainers WHERE trainer_id IN (SELECT trainer_id FROM badges);

Read it as: "keep the trainers whose trainer_id appears in that list". The list contains 1 three times and 2 five times, but that changes nothing: IN only checks whether a value is present, so each trainer comes out once. Unlike a scalar subquery, this one may return many rows — but always one column.

Same result with a JOIN

SELECT DISTINCT t.name FROM trainers t INNER JOIN badges bd ON bd.trainer_id = t.trainer_id returns the same three names (DISTINCT is required there, because the join repeats Alex three times). Prefer IN when you only need columns from one table; use a JOIN when you also want columns from the other table, like the gym or the date.

Exercise 2 — NOT IN: missing from the list

Negate it, and you get the trainers who never earned a badge:

SELECT name FROM trainers WHERE trainer_id NOT IN (SELECT trainer_id FROM badges);

That is yet another way to find missing rows, next to LEFT JOIN … IS NULL and EXCEPT — and it reads closest to the question in plain words: "trainers whose id is not in the badge list".

One column, no more

The subquery of an IN must return exactly one column. IN (SELECT trainer_id, gym_id FROM badges) fails with sub-select returns 2 columns - expected 1.

Exercise 3 — Nested lists

Subqueries can nest. Which trainers does this query return?

SELECT name FROM trainers WHERE trainer_id IN ( SELECT trainer_id FROM badges WHERE gym_id IN (SELECT gym_id FROM gyms WHERE city = 'Azure City') );
Options
• Alex and Morgan: the ones with a badge from an Azure City gym
• Morgan and Jordan: the trainers who live in Azure City
• Morgan only
• All four trainers

Hint : Work from the inside out: gyms → badges → trainers.

Exercise 4 — The NOT IN trap

Now the opposite question: which trainers own no Codémon? Jordan has none, so Jordan should come out. Run the query:

SELECT name FROM trainers WHERE trainer_id NOT IN (SELECT trainer_id FROM codemon);

No rows — not even Jordan. Look at the list the subquery produces: SELECT trainer_id FROM codemon gives 1, 1, 2, 2, 3, 3, 3 and… NULL, because Plantachu is wild and has no trainer.

For Jordan, NOT IN checks that 4 is different from every value in the list. 4 is indeed different from 1, 2 and 3 — but is 4 different from NULL? SQL cannot tell: a comparison with NULL is neither true nor false, it is *unknown*. See for yourself:

SELECT 4 <> 1 AS vs_one, 4 <> NULL AS vs_null;

A single *unknown* in the chain is enough: the condition is never true, and nobody comes out. The fix: remove the NULLs from the list before negating.

SELECT name FROM trainers WHERE trainer_id NOT IN (SELECT trainer_id FROM codemon WHERE trainer_id IS NOT NULL);

Rule of thumb

IN is safe with NULLs; NOT IN is not. Always add WHERE column IS NOT NULL inside a NOT IN subquery — or use NOT EXISTS, which you will meet in the next chapter.

Exercise 5 — Badges of March

Complete the query so that it lists the gyms where at least one badge was earned in March 2023 (dates on or after '2023-03-01').

Hint : The list must contain gym ids, since it is compared with gym_id.

Exercise 6 — Spot the risky one

All four queries start with SELECT name FROM trainers WHERE trainer_id. Which one is at risk of returning no rows at all because of a NULL?

Options
• NOT IN (SELECT trainer_id FROM codemon)
• NOT IN (SELECT trainer_id FROM badges)
• NOT IN (SELECT winner_id FROM battles)
• IN (SELECT trainer_id FROM codemon)

Hint : Which subquery column can be NULL in this database? Think of the wild Codémon.

Exercise 7 — Same start, four endings

Every query starts with SELECT name FROM trainers. Match each WHERE clause with its result. Reminder: the only fire gym is in Azure City.

Items to match
• WHERE trainer_id IN (SELECT winner_id FROM battles)
• WHERE trainer_id NOT IN (SELECT winner_id FROM battles)
• WHERE trainer_id NOT IN (SELECT trainer_id FROM codemon)
• WHERE hometown IN (SELECT city FROM gyms WHERE type = 'fire')
Definitions
• Alex, Morgan, Taylor
• Jordan
• No rows (a NULL in the list)
• Morgan, Jordan

Exercise 8 — Home-field advantage

Professor Pi wonders which Codémons could challenge an Azure City gym of their own type.

Return name, type and level of the Codémons whose type is the type of at least one gym located in Azure City. No JOIN: use IN with a subquery. Sort by level descending.

Starter code
-- Write your query below
Back to the course