🔎 FilteringbeginnerCommonly used

How to use IN and NOT IN in SQL

Match a column against a list of values.

Match a list of valuesAll databases

SELECT name, country
FROM users
WHERE country IN ('Kenya', 'Nigeria', 'Ghana');

Cleaner alternative to multiple OR conditions. Matches any value in the list.

Exclude a list of valuesAll databases

SELECT name, country
FROM users
WHERE country NOT IN ('Kenya', 'Nigeria');

Returns rows where the column does not match any value in the list.

NOT IN with NULLs in the list returns no rows. Use NOT EXISTS or IS NOT NULL checks.

IN with a subqueryAll databases

SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE total > 1000
);

Matches against the result of a subquery. Equivalent to EXISTS in many cases.

When to use: Filtering based on whether a related record exists.

NOT IN with a subqueryAll databases

SELECT name, email
FROM users
WHERE id NOT IN (
  SELECT user_id
  FROM orders
  WHERE created_at > NOW() - INTERVAL '1 year'
);

Finds users who have NOT placed an order in the last year.

If the subquery can return NULL, use NOT EXISTS instead — NOT IN with any NULL returns empty.

Common Mistakes

→NOT IN with NULLs — if the subquery returns any NULL, NOT IN returns zero rows. Use NOT EXISTS instead.
→Large IN lists — use a JOIN or temporary table for thousands of values.