Sql - IN and NOT IN
Code Life
By Pierre Marsaa
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:
↗️
Loading editor...
↶
↷
That is a list of ids. Put this query in parentheses after IN, and SQL uses its result as the list:
↗️
Loading editor...
↶
↷
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.
Verify