Matching Multiple Values with IN

Explanation

Sometimes you need to filter rows where a column matches one of several possible values. You could write this with a chain of OR conditions, but that gets repetitive and hard to read quickly. The IN operator gives you a cleaner way to say "match any of these values."

For example, if you wanted every course in the courses table whose subject is "Math" or "Science," you could write:

WHERE subject = 'Math' OR subject = 'Science'

Or, using IN, the same logic becomes:

WHERE subject IN ('Math', 'Science')

Both return identical results, but the second version scales much better once you're checking against five, ten, or more values.

Syntax

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

You can also negate it to exclude a list of values:

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

Interactive Example

Common Mistakes

• Forgetting quotes around text values. IN (Math, Science) will fail or be interpreted incorrectly, text values need quotes: IN ('Math', 'Science').
• Using NOT IN with a list that might contain missing data. If any value in the list turns out to be NULL, NOT IN can behave in surprising ways. This matters more once you build lists from subqueries later in the course.
• Writing a giant OR chain instead. It works, but it's harder to read and easier to make a copy-paste mistake in, prefer IN whenever you're checking a single column against multiple values.

Best Practices

• Reach for IN any time you catch yourself writing three or more OR conditions on the same column.
• Keep the list of values readable, format long IN lists across multiple lines if needed.
• Use NOT IN to exclude a small, known set of values, but double check that the list can't contain NULL.

Practice Question

Using the playground above, write a query that returns the name and subject of every course in the courses table where the subject is either "Science" or "Art".

Summary

The IN operator lets you match a column against a list of values in a single, readable condition. It's equivalent to a chain of OR conditions, but far easier to read and maintain, especially as the list of values grows.

FAQ