IN and NOT IN for list matching

Concepts covered: Set Membership

The next request from Rachel covered 2 departments at once: everyone in Marketing or Sales. = compares a column to 1 value, so it cannot express a request about 2. Writing department = 'Marketing', 'Sales' is a syntax error, and writing 2 separate conditions needs a connector that the intermediate tier teaches. IN is the operator built for this job: a column on the left, a parenthesised list on the right, and the row passes when the stored value matches any entry in the list.

One column, many values

SELECT emp_name, department
FROM employees
WHERE department IN ('Marketing', 'Sales')

Each row's department is checked against the list one entry at a time, with the same exact-match rule = uses: character for character, case included. Osei and Tanaka hit 'Marketing'. Ruiz and Adams hit 'Sales', which makes 4 rows that pass. The 3 Engineering rows and Novak in HR match neither entry and fail. One hit anywhere in the list is enough, and the database stops checking a row the moment it finds a match.

The complement

NOT IN flips the test: the row passes when its value matches none of the entries. department NOT IN ('Marketing', 'Sales') returns the 4 rows the first query dropped, the 3 Engineering rows plus Novak. As with = and !=, the 2 forms split the table between them, 4 rows each here. The list can hold numbers as well as text, written bare: employee_id IN (1, 4, 8) picks Chen and Osei directly by key, along with Novak, which is the usual way to pull a handful of specific records when their ids are already known.

SELECT emp_name, department
FROM employees
WHERE department IN ('Marketing', 'Sales')

Add 'HR' to the list and Novak appears, 5 rows. Replace IN with NOT IN and the result flips to the Engineering team alone. Shrink the list to the single entry IN ('Sales') and the result is Ruiz and Adams, identical to department = 'Sales': a list of 1 behaves exactly like =, which is why IN is a safe choice from the start when the list of accepted values is likely to grow.

A misspelled entry fails quietly

Every entry in the list is its own exact match, so an entry with a typo matches nothing, and the database cannot tell a misspelling from a value that has no rows yet. A list meant to cover Marketing and Sales plus the newly created Finance team, written as department IN ('Marketing', 'sales', 'Finance'), runs without an error and returns 2 rows.

Finance matching 0 rows is correct, since the team has no hires yet. 'sales' matching 0 rows is a bug that drops Ruiz and Adams, and the result looks the same in both cases. The check for a hand-written list is to compare the departments that came back against the entries that went in: 3 entries and 1 department returned means 2 entries matched nothing, and reading the column's stored values with no filter shows which of them is a typo.

NOT IN behaves in a surprising way when the list itself contains a NULL, returning no rows at all. That trap has a section of its own in the advanced tier of this track; the lists in this lesson are written by hand and contain no NULL.

About this lesson

This section is part of the Filtering: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.