SQL IN

Checking a value against several acceptable options with OR gets repetitive fast. IN says the same thing more directly: match if the value is any of these.

Learning Objectives

Use IN to match a value against a list.
Use NOT IN to exclude a list of values.
Recognize the specific danger of NOT IN when the list contains NULL.

Value List

('Engineering', 'Research')

Matches any value in the list

Matching Rows

department is one of the listed values

Basic IN

Any number of values, comma-separated. IN syntax and behavior is identical across every database in this course.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT first_name, department
FROM employees
WHERE department IN ('Engineering', 'Research');

-- Equivalent to:
-- WHERE department = 'Engineering' OR department = 'Research';

NOT IN

Excludes every row whose value matches anything in the list, employees in Marketing, Sales, or any other department.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT first_name, department
FROM employees
WHERE department NOT IN ('Engineering', 'Research');

The NOT IN and NULL Gotcha

If the list IN or NOT IN compares against contains even a single NULL, NOT IN silently returns zero rows, for every row, with no error. This is because SQL evaluates NOT IN as a series of <> comparisons joined by AND, and a NULL anywhere in that chain makes the entire AND chain evaluate to unknown rather than true, the same NULL-comparison behavior covered earlier in this course. This most commonly bites people when the list comes from a subquery (a query nested inside another, taught in full in Module 6) rather than a list typed out by hand, since it's easy not to notice a NULL hiding in a subquery's results.

A plain list with a NULL in it, returns zero rows

SELECT first_name
FROM employees
WHERE department_id NOT IN (1, 2, NULL);

-- Matches nothing at all, even rows where department_id is 5, 6, or anything else

The same problem shows up with a subquery (covered in Module 6)

SELECT first_name
FROM employees
WHERE department_id NOT IN (SELECT department_id FROM project_assignments);
-- If even one row in project_assignments has a NULL department_id, this returns zero rows for everyone

NOTE

Plain IN doesn't have this problem, a NULL in an IN list is simply never matched but doesn't break the other comparisons. The danger is specific to NOT IN.

Try It Yourself

Write a query returning employees NOT in Engineering, Research, or Marketing.

Answer

SELECT first_name, department
FROM employees
WHERE department NOT IN ('Engineering', 'Research', 'Marketing');

Frequently Asked Questions

Can IN be used with a subquery instead of a literal list?

Yes, WHERE department_id IN (SELECT id FROM departments WHERE ...) is extremely common, covered in full in the Subqueries module later in this course.

Is IN faster than a chain of OR comparisons?

Functionally they're treated the same by most query optimizers, performance is rarely the deciding factor, readability is. IN simply reads more clearly once there are more than two or three options.

Common Beginner Mistakes

Using NOT IN with a subquery that might return NULL

This is one of the most common silent SQL bugs: a single NULL in the subquery's results makes NOT IN match nothing at all, for every row, with no error raised. Filtering the subquery with WHERE column IS NOT NULL, or using NOT EXISTS instead (covered later in this course), avoids the trap entirely.

Writing out a long OR chain instead of IN

WHERE department = 'A' OR department = 'B' OR department = 'C' works but is repetitive and error-prone to maintain. WHERE department IN ('A', 'B', 'C') says the same thing more clearly.

Best Practices

Reach for IN instead of a chain of OR = comparisons once checking against more than two values.
Be specifically cautious with NOT IN against a subquery, filter out NULLs first or use NOT EXISTS instead.
Keep an IN list reasonably sized, an extremely long literal list is usually a sign a subquery or join would express the intent better.

Interview Questions

Why can WHERE id NOT IN (SELECT ...) return zero rows unexpectedly, even when it looks correct?

If the subquery's result includes even one NULL, NOT IN's underlying chain of <> comparisons evaluates to unknown for every row, because comparing anything to NULL is never true or false. The entire WHERE clause then matches nothing, silently, with no error. The safe fix is NOT EXISTS, or filtering the subquery to exclude NULLs explicitly.

Rewrite WHERE department = 'Engineering' OR department = 'Research' OR department = 'Marketing' using IN.

WHERE department IN ('Engineering', 'Research', 'Marketing'); — functionally identical, more concise, and easier to extend with another value later.

Summary

This lesson covered matching against a list of values with IN and NOT IN, and the specific, well-known danger of combining NOT IN with a subquery that can return NULL.

What's Next?

The next lesson covers BETWEEN, matching a value that falls within a range.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.