Lesson 3.4

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 does the opposite: it removes rows whose value appears in the list.

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 column_name 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

Do not use IN for unrelated conditions. UseOR when the alternatives involve different columns.
Be careful with NOT IN when the list or subquery can containNULL. A single NULL can make every comparison unknown. See why NOT IN with NULL returns nothing.
When you mix AND and OR, add parentheses. Seewhy OR without parentheses changes your filter.