08 — IN

One line: IN checks whether a value matches any item in a list — a clean shorthand for a pile of ORs.

The idea in plain English

When you want "is this value one of these several options?", writing it with OR gets repetitive fast:

WHERE name = 'Zach' OR name = 'David' OR name = 'Claire'

IN says the same thing, readably:

WHERE name IN ('Zach', 'David', 'Claire')

It's literally a chain of ORs under the hood — match any value in the list and the row is returned. NOT IN flips it: return rows that match none of them.

Syntax

-- Strings: quoted, comma-separated, inside parentheses
SELECT * FROM customer
WHERE first_name IN ('John', 'Jake', 'Julie');

-- Numbers: NO quotes (match the column's data type)
SELECT * FROM payment
WHERE amount IN (0.99, 1.98, 1.99);

-- Exclude a set
SELECT * FROM payment
WHERE amount NOT IN (0.99, 1.98, 1.99);

Two rules to remember: parentheses around the list, commas between items, and the list values must match the column's type — quote strings, leave numbers bare.

-- Count matches vs non-matches
SELECT COUNT(*) FROM payment WHERE amount IN (0.99, 1.98, 1.99);      --> 3301
SELECT COUNT(*) FROM payment WHERE amount NOT IN (0.99, 1.98, 1.99);  --> 11295

Handy habit: SELECT DISTINCT amount FROM payment ORDER BY amount; first, to see the values before you build your IN list.

The NOT IN + NULL trap ⚠️ (career-saver)

This is one of the most infamous bugs in SQL. If the list contains a NULL, NOT IN returns no rows at all — even rows that obviously shouldn't be excluded.

-- Looks fine. Returns ZERO rows. 😱
SELECT * FROM film
WHERE rating NOT IN ('R', 'PG-13', NULL);

Why: for a 'G' film, SQL evaluates 'G' NOT IN ('R','PG-13',NULL) as NOT ('G'='R' OR 'G'='PG-13' OR 'G'=NULL) → NOT (false OR false OR **unknown**) → NOT (unknown) → unknown. And WHERE only keeps rows that are strictly true, so nothing survives.

• IN with a NULL in the list is mostly harmless (that entry just never matches).
• NOT IN with a NULL in the list is poison — it silently wipes your result set.

This matters most with subqueries (a later lesson): WHERE id NOT IN (SELECT col FROM t) breaks the instant col contains a single NULL. The safe alternatives are NOT EXISTS or adding WHERE col IS NOT NULL to the subquery. File this away now — it's a bug people spend hours on.

Production notes

• IN beats stacked ORs for readability — and it's the fix for the "each side of OR needs a full condition" mistake from the WHERE lesson. rating IN ('G','PG','PG-13') vs rating = 'G' OR rating = 'PG' OR rating = 'PG-13'.
• IN with a subquery is where it gets powerful (coming up): WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount > 10). That's how you filter one table by results from another.
• Order and duplicates in the list don't matter. IN (1, 2, 2) behaves like IN (1, 2).
• Very long IN lists get slow. A hardcoded list of hundreds/thousands of values is a smell — usually better as a join against a temp table or a subquery.
• Type mismatches can error or silently coerce. Putting '0.99' (quoted) in a numeric IN list may work via implicit casting on some engines but is fragile — always match the column's real type.

Challenges

All use the DVD Rental database.

Challenge 1 — Warm-up

Count how many films are rated either G or PG. (Table: film, column rating.)

SELECT COUNT(*) FROM film
WHERE rating IN ('G', 'PG');

Challenge 2 — Exclude a set (NOT IN)

Return the title and rating of every film that is not rated G or PG (i.e. the more mature titles).

SELECT title, rating
FROM   film
WHERE  rating NOT IN ('G', 'PG');

Challenge 3 — Spot the bug 🔎 (the NULL trap)

A teammate wants all films that are not rated R or PG-13. They defensively toss a NULL into the list "just in case," and now the query returns nothing:

SELECT * FROM film
WHERE rating NOT IN ('R', 'PG-13', NULL);

Explain why, and give the correct query.

SELECT * FROM film
WHERE rating NOT IN ('R', 'PG-13');

30-second recap