IN & NOT IN (Membership)

"Show players from MI or CSK." With OR you'd write team = 'MI' OR team = 'CSK' — fine for two teams, painful for ten. IN lets you give a list instead.

Definition

IN tests whether a value matches any value in a list (or in the result of a subquery). expr IN (v1, v2, v3) is shorthand for expr = v1 OR expr = v2 OR expr = v3.

NOT IN tests that a value matches none of the list values: expr NOT IN (v1, v2) means expr <> v1 AND expr <> v2.

The "shorthand" is the key: IN is a chain of = joined by OR, and NOT IN is a chain of <> joined by AND. That second fact explains the NULL trap below.

Syntax

SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_list
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

-- the list can also come from a subquery (one column)
WHERE column_name IN (SELECT column_name FROM other_table WHERE ...);

Example — IN: teams MI or CSK

SELECT *
FROM players
WHERE team IN ('MI', 'CSK');

IN is a short way to write team = 'MI' OR team = 'CSK'.

Example — NOT IN: everyone else

SELECT *
FROM players
WHERE team NOT IN ('MI', 'CSK');

NOT IN keeps everything that is not in the list.

Example — IN with a subquery

Players whose team is based in Mumbai or Chennai (the list is built from the teams table):

SELECT name, team
FROM players
WHERE team IN (SELECT team FROM teams WHERE city IN ('Mumbai', 'Chennai'));

⚠️ The NOT IN + NULL trap

Which franchises have no entry in player_profiles? One player there (David Warner) has team = NULL.

SELECT team, team_name
FROM teams
WHERE team NOT IN (SELECT team FROM player_profiles);

Result: 0 rows! Why? The subquery returns ('RCB', 'MI', 'RR', 'CSK', NULL, 'MI'). For KKR, NOT IN expands to 'KKR' <> 'RCB' AND ... AND 'KKR' <> NULL. The last test is UNKNOWN, and TRUE AND UNKNOWN = UNKNOWN → every row is dropped. One NULL in a NOT IN list wipes out the whole result.

✅ Fix — remove NULLs from the list:

SELECT team, team_name
FROM teams
WHERE team NOT IN (SELECT team FROM player_profiles WHERE team IS NOT NULL);

(NOT EXISTS is another safe alternative — covered with subqueries.)

Note: IN is not affected the same way: team IN ('MI', NULL) still returns the MI rows, because TRUE OR UNKNOWN = TRUE. Also, a row whose own value is NULL never matches IN or NOT IN.

Common mistakes

• ❌ WHERE team IN 'MI', 'CSK' → brackets required. ✅ IN ('MI', 'CSK')
• ❌ WHERE team IN ('MI, CSK') → that is ONE string 'MI, CSK'. ✅ quote each value separately.
• ❌ NOT IN (subquery) when the subquery column can be NULL → 0 rows. ✅ add WHERE col IS NOT NULL or use NOT EXISTS.
• ❌ WHERE team IN (NULL) to find NULLs → never matches. ✅ team IS NULL.

Interview tip

Q: Why can NOT IN return no rows even though matches clearly exist? A: If the list or subquery contains a NULL, x NOT IN (...) becomes ... AND x <> NULL, which is UNKNOWN for every row. Filter NULLs out of the subquery or use NOT EXISTS.