SQL online course

SQL IN Operator

Learn how to use the SQL IN operator to filter rows by matching a column against a list of text or numeric values, subqueries, and NULL-safe conditions.

What the SQL IN Operator Does

The IN operator is a membership operator. It tests whether an expression matches any value in a specified set. In a WHERE clause, it keeps rows whose column value appears in a value list.

For example, this query finds employees whose last name is either Patterson or Bow:

SELECT lastName, firstName, extension
FROM employees
WHERE lastName IN ('Patterson', 'Bow');

The value list contains two comma-separated text values. A row qualifies when lastName equals at least one of them.

IN is a concise alternative to repeating equality comparisons with OR. Instead of writing:

WHERE lastName = 'Patterson'
   OR lastName = 'Bow'

you can write:

WHERE lastName IN ('Patterson', 'Bow')

Both predicates express the same logic when they compare the same column using equality.

Basic IN Syntax

SELECT column_list
FROM table_name
WHERE column_name IN (value_1, value_2, value_3);
  • SELECT identifies the columns to return.
  • FROM identifies the source table.
  • WHERE introduces the row-filtering condition.
  • column_name is the expression being tested.
  • IN tests membership in the following set.
  • The values must be inside parentheses and separated by commas.

Text values normally need string delimiters, usually single quotation marks. Numeric values are normally written without quotation marks:

WHERE lastName IN ('Patterson', 'Bow')
WHERE officeCode IN (1, 4, 7)

Use values that are compatible with the tested column. A character column should be compared with character values, and a numeric column should be compared with numeric values. Exact conversion rules can differ between database systems, so do not rely on implicit conversions when a clear data type is available.

Example: Filter Employees by Several Surnames

Assume an employees table with these columns:

  • lastName
  • firstName
  • extension

A small sample of the data is:

  • Murphy, Diane, x5800
  • Patterson, Mary, x4611
  • Patterson, William, x4871
  • Bow, Anthony, x5428
  • Patterson, Steve, x4334
  • Firrelli, Jeff, x9273

Run this query:

SELECT lastName, firstName, extension
FROM employees
WHERE lastName IN ('Patterson', 'Bow');

The result contains these matching rows:

  • Mary Patterson, x4611
  • William Patterson, x4871
  • Anthony Bow, x5428
  • Steve Patterson, x4334

IN does not return only one row for each value in the list. Every database row whose lastName matches either surname qualifies. Therefore, all three Patterson rows are returned.

IN Compared with OR

The equivalent OR-based query is:

SELECT lastName, firstName, extension
FROM employees
WHERE lastName = 'Patterson'
   OR lastName = 'Bow';

This produces the same qualifying rows as the IN query. IN is usually easier to read when one expression is compared with several possible values:

  • lastName IN ('Patterson', 'Bow') clearly presents a set of accepted surnames.
  • The OR version repeats lastName for every comparison.

OR remains useful when the conditions involve different columns or different kinds of tests. For example, this condition cannot be reduced to one simple IN list:

WHERE lastName = 'Patterson'
   OR officeCode = 7

Here, the two alternatives test different expressions.

Understanding the Result

IN controls which rows qualify; it does not control how many rows are returned or their display order.

  • Multiple source rows can match the same list value.
  • Every matching row is returned unless another clause changes the result.
  • DISTINCT can remove duplicate result rows.
  • A database-specific row limit such as LIMIT or a FETCH clause can restrict the number of rows.
  • Without ORDER BY, the source table's existing order is not guaranteed.

For a predictable sequence, add an explicit ordering clause:

SELECT lastName, firstName, extension
FROM employees
WHERE lastName IN ('Patterson', 'Bow')
ORDER BY lastName, firstName;

Text and Numeric Values

Character lists and numeric lists use different literal notation.

-- Character values
WHERE lastName IN ('Patterson', 'Bow')

-- Numeric values
WHERE officeCode IN (1, 4, 7)

Do not assume that a quoted number and an unquoted number behave identically in every database. The database may convert values, reject the comparison, or apply rules that affect index use. Match the list values to the column's data type whenever possible.

String comparison behavior can also vary. Case sensitivity, trailing spaces, accent handling, and collation rules depend on the database system and column configuration. If a value appears to match but returns no rows, inspect the actual stored values and verify the system's comparison rules.

Combining IN with Other Conditions

IN can be combined with other predicates using AND or OR. This query selects the two surnames and requires a known extension:

SELECT lastName, firstName, extension
FROM employees
WHERE lastName IN ('Patterson', 'Bow')
  AND extension IS NOT NULL
ORDER BY lastName, firstName;

Parentheses are important when combining AND, OR, and membership tests. They make the intended grouping explicit and prevent a query from relying on the reader's interpretation of operator precedence:

SELECT lastName, firstName, officeCode
FROM employees
WHERE (lastName IN ('Patterson', 'Bow') AND officeCode = 1)
   OR lastName = 'Murphy';

This means either the employee has one of the listed surnames and works in office 1, or the employee is Murphy. Without parentheses, a mixture of AND and OR can be misunderstood and may not represent the desired business rule.

NULL and IN

NULL represents an unknown or absent value. It is not an ordinary value and cannot be matched with the normal equality behavior used by an IN list.

This condition does not retrieve rows where extension is NULL:

WHERE extension IN ('x4611', NULL)

To find missing extensions, use IS NULL:

SELECT lastName, firstName, extension
FROM employees
WHERE extension IS NULL;

If the goal is to match either a known value or a missing value, write separate predicates:

WHERE lastName IN ('Patterson')
   OR lastName IS NULL

SQL uses three-valued logic: a predicate can evaluate to true, false, or unknown. Comparisons involving NULL often produce unknown rather than true or false. A row is retained by WHERE only when its condition evaluates to true.

Remember these patterns:

  • column IN ('A', 'B') matches known listed values; a NULL column does not qualify.
  • column IS NULL directly finds NULL values.
  • Adding NULL to an IN list does not replace IS NULL.

NOT IN Overview

NOT IN is intended to exclude values present in a list:

SELECT lastName, firstName
FROM employees
WHERE officeCode IS NOT NULL
  AND officeCode NOT IN (1, 4);

This returns employees assigned to offices other than 1 and 4. The explicit IS NOT NULL condition states how rows with an unknown office code should be treated.

For exclusion queries involving a nullable subquery result, explicitly remove NULLs from the subquery when appropriate, or consider NOT EXISTS. NOT EXISTS checks whether a related matching row exists and avoids the same simple membership trap when its correlation is written correctly.

IN with a Subquery

A subquery is a query nested inside another SQL statement. IN can compare an outer expression with the result of a subquery instead of with a manually typed value list.

The subquery must return one compatible column:

SELECT lastName, firstName, officeCode
FROM employees
WHERE officeCode IN (
  SELECT officeCode
  FROM offices
  WHERE country = 'USA'
);

The inner query produces office codes for offices in the United States. The outer query returns employees whose officeCode appears in that result set.

The selected subquery column must be compatible with the outer expression. A scalar IN test such as officeCode IN (...) expects one column from the subquery, not multiple unrelated columns. If a subquery returns multiple columns, the database may report a column-count error. Select only the required compatible column, or use a row-value comparison only where the database supports that form.

Common Troubleshooting Cases

A Value Appears to Match but Returns No Rows

Check for different casing, leading or trailing whitespace, collation behavior, or a different stored spelling. Also confirm that the list literal has the correct data type. Inspect distinct stored values and consult the comparison rules for the database system.

IN with NULL Does Not Find Missing Values

NULL is not an ordinary list member for comparison purposes. Replace the attempted NULL list item with an explicit predicate:

WHERE lastName IN ('Patterson')
   OR lastName IS NULL

NOT IN Returns Zero Rows

Check whether the list or subquery contains NULL. Because of three-valued logic, the NOT IN predicate may become unknown for every candidate row. Filter NULL values from the subquery if that matches the intended rule, handle NULL explicitly, or rewrite the exclusion with NOT EXISTS.

Rows Appear in an Unexpected Sequence

IN does not sort results. Add an ORDER BY clause with the columns that define the desired order.

An IN Subquery Produces a Column-Count Error

Verify that the subquery selects exactly one compatible column:

WHERE officeCode IN (
  SELECT officeCode
  FROM offices
  WHERE country = 'USA'
);

Exam- and Practice-Relevant Notes

  • IN means “matches any member of this set,” not “returns one row per member.”
  • The list is parenthesized and comma-separated.
  • Use quotes around text literals and normally no quotes around numeric literals.
  • IN and a chain of equality comparisons connected with OR are equivalent when they test the same expression.
  • Use IS NULL rather than IN (NULL) to find missing values.
  • Be especially careful with NULL and NOT IN, particularly when a subquery can return NULL.
  • A scalar IN subquery should return one compatible column.
  • Use ORDER BY when output order matters.

Once basic filtering is comfortable, review the SELECT statement, the AND and OR operators, and BETWEEN to compare other ways of expressing row conditions.