Lesson 3.3

SQL IN vs OR: IN and NOT IN Operators

Lists of allowed or excluded values are clearer with IN than repeated OR checks.

When one column can match several values, IN keeps the query shorter and easier to scan. It asks whether a value appears in a list.

NOT IN excludes values found in the list, but it isn't a perfect logical opposite when nulls are involved. The null trap is covered below.

SQL IN vs OR

IN and repeated OR checks can mean the same thing when every comparison is against the same column. Use IN for that case because it is shorter and makes the list of allowed values obvious.

Same result, easier to scan

-- Repeated OR checks
where state = 'PA'
   or state = 'OR'
   or state = 'FL'

-- Equivalent IN list
where state in ('PA', 'OR', 'FL')

Pattern	Best when	Example
`IN`	One column can match several known values.	`state in ('PA', 'OR', 'FL')`
`OR`	Different columns or different kinds of conditions can match.	`price > 20 or quantity_on_hand < 10`
Parentheses	You mix `OR` with `AND`.	`(state = 'PA' or state = 'OR') and is_active`

Keep the list short and explicit

IN is best for a small, known set of values such as states, statuses, or categories. If the list is coming from another table, a join or subquery is usually clearer than copying many values into the SQL by hand.

Pattern

select column_one, column_two
from table_name
where column_name in ('value one', 'value two')
  and other_column not in ('value three')

Ready to run it?

Open the DuckDB playground with the matching dataset and query already filled in.

Try WHERE IN / OR in the playground

Common mistakes

Don't use IN for unrelated conditions. Use OR when the alternatives involve different columns.
Be careful with NOT IN when the list or subquery can contain NULL. A single NULL means NOT IN can never be true. See why NOT IN with NULL returns nothing.
When you mix AND and OR, add parentheses. See why OR without parentheses changes your filter.