VMware ESXi and vSphere Cluster Management

SQL IN Operator: Filter Rows by Multiple Values

Learn how to use SQL IN and NOT IN in WHERE clauses, compare IN with OR, handle NULL values, and filter with subqueries.

The SQL IN operator tests whether an expression matches any value in a supplied set. It is commonly used in a WHERE clause, the part of a query that filters rows according to a condition.

For example, instead of writing several equality comparisons joined with OR, you can write one concise IN condition:

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

A condition such as this is called a predicate. For each row, the predicate evaluates to true, false, or unknown.

Basic IN syntax

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

SELECT column_list specifies the columns to return. Use SELECT * to return every column, or name only the columns needed.
FROM table_name identifies the table being queried.
WHERE begins the row-filtering condition.
column_name is the expression being tested.
The parentheses contain the value list: comma-separated values that are acceptable matches.
Each listed value must be compatible with the tested column's data type.

The predicate is true when the tested value equals at least one item in the list.

Matching text values

Suppose an employees table contains these rows:

lastName   firstName   extension
Murphy     Diane       x5800
Patterson  Mary        x4611
Patterson  William     x4871
Bow        Anthony     x5428
Patterson  Steve       x4334

To retrieve every employee whose last name is Patterson or Bow, use:

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

Text values are string literals. In standard SQL, string literals are written with single quotes.

The query returns these matching rows:

lastName   firstName   extension
Patterson  Mary        x4611
Patterson  William     x4871
Bow        Anthony     x5428
Patterson  Steve       x4334

Each returned row matches one of the values in the list: the Patterson rows match 'Patterson', and Anthony Bow matches 'Bow'. Murphy is excluded because 'Murphy' is not in the list.

Select only the columns you need

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

This applies the same filter but returns a narrower result set. Selecting specific columns is often clearer and avoids transferring unnecessary data.

IN compared with OR

The equivalent condition using repeated equality comparisons is:

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

For ordinary non-NULL values, this produces the same matching rows as the IN query. IN is usually easier to read when the same column is compared with several possible values.

-- Concise form
WHERE lastName IN ('Patterson', 'Bow')

-- Equivalent OR form
WHERE lastName = 'Patterson'
   OR lastName = 'Bow'

OR remains useful when the conditions are different, such as comparisons involving different columns or operators.

WHERE lastName = 'Patterson'
   OR employeeNumber > 1100

Here, the conditions do not form a simple list of values for one column, so IN is not a direct replacement.

Using IN with other data types

IN can test numbers as well as text:

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE employeeNumber IN (1002, 1056, 1088);

Numeric literals are written without quotes. Avoid quoting numbers unless the column is actually character-based. Quoted values can cause implicit type conversion, errors, or inefficient comparisons depending on the database system.

Date and text literal syntax can vary between database systems. Follow the rules for your database, and make sure the values are compatible with the type of the column being tested.

Text example: lastName IN ('Patterson', 'Bow')
Numeric example: employeeNumber IN (1002, 1056, 1088)
Date example: use the date-literal format supported by your database.

NOT IN

NOT IN is the negated form of IN. It selects rows whose value is not equal to any value in the list.

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

This excludes employees with either of those last names. However, a row whose lastName is NULL is not automatically included, because the comparison result is unknown.

Important: NOT IN can produce no rows unexpectedly when its list or subquery contains NULL. When a subquery is involved, consider filtering out NULL values or using NOT EXISTS.

IN with a subquery

Instead of a literal value list, the parenthesized source can be a subquery, which is a query nested inside another SQL statement.

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

The inner query returns a single column containing office codes for offices in the USA. The outer query returns employees whose officeCode appears among those results.

An IN subquery must return exactly one column, and that column must have a compatible data type with the outer expression. The subquery may return multiple rows.

Use a literal list when the accepted values are a small, known set: status IN ('new', 'open').
Use a subquery when the accepted values come from current database data: officeCode IN (SELECT officeCode ...).

NULL and SQL three-valued logic

NULL represents an unknown or missing value. It is not the same as zero, an empty string, or a normal text value.

SQL uses three-valued logic: a predicate can be true, false, or unknown. A WHERE clause returns only rows for which its predicate is true.

Do not use IN (NULL) to find missing values

-- Incorrect for finding NULL values
WHERE extension IN (NULL)

IN (NULL) does not match rows where extension is NULL. Equality-style comparisons with an unknown value produce unknown rather than true.

Use IS NULL instead:

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

Similarly, use IS NOT NULL when you need values that are present. Do not write extension = NULL to find missing values.

Why NULL affects NOT IN

Consider a subquery used with NOT IN. If the subquery returns values such as 10, 20, and NULL, SQL cannot establish that an outer value is different from every returned value. The predicate can therefore evaluate to unknown for every outer row, causing the query to return no rows.

-- Potentially unsafe when officeCode can be NULL
WHERE officeCode NOT IN (
  SELECT officeCode
  FROM offices_to_skip
);

When appropriate, exclude nulls from the subquery:

WHERE officeCode NOT IN (
  SELECT officeCode
  FROM offices_to_skip
  WHERE officeCode IS NOT NULL
);

Another common solution is NOT EXISTS, which can express the exclusion without the same NULL trap:

WHERE NOT EXISTS (
  SELECT 1
  FROM offices_to_skip AS skipped
  WHERE skipped.officeCode = employees.officeCode
);

Practical query-writing guidance

Format longer lists clearly

Put one value per line when a list is long enough to be difficult to scan:

SELECT employeeNumber, lastName
FROM employees
WHERE employeeNumber IN (
  1002,
  1056,
  1088,
  1102
);

Handle empty lists

An empty list such as IN () is invalid in many SQL dialects. Application code should handle an empty collection before constructing the query. Decide explicitly whether an empty collection should mean no matching rows or no filtering, then generate a safe query for that behavior.

Use parameters for application input

When values come from an application, use a parameterized query rather than concatenating user-provided text into SQL. A parameterized query supplies runtime values separately from the SQL text, reducing syntax problems and helping prevent SQL injection.

-- Conceptual form; parameter syntax varies by driver
SELECT employeeNumber, lastName
FROM employees
WHERE employeeNumber IN (?, ?, ?);

Your database driver may use named parameters or a different placeholder style. Bind the values through the driver instead of inserting them into the SQL string.

Consider another design for very large lists

A very large hard-coded list can be difficult to maintain and may be inefficient. Depending on the database and application, store the values in a temporary or staging table, join to that table, or use a subquery. These approaches make the values data rather than a large SQL expression.

Common problems and fixes

Text does not match: use single-quoted values such as IN ('Patterson', 'Bow'), then check spelling, case behavior, and extra whitespace in the stored data.
IN (NULL) returns no missing rows: use IS NULL.
NOT IN with a subquery returns no rows: check whether the subquery returns NULL; filter it out or rewrite the condition with NOT EXISTS.
The subquery raises an error: select exactly one compatible column inside the IN subquery.
A large list is slow or hard to maintain: move the values into a table and use a join or subquery.
Generated SQL contains IN (): handle the empty input case before executing the statement.

Quick reference

-- Literal list
SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, ...);

-- Exclusion list
SELECT column_list
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

-- One-column subquery
SELECT column_list
FROM table_name
WHERE column_name IN (
  SELECT related_column
  FROM related_table
  WHERE condition
);

-- Find missing values
SELECT column_list
FROM table_name
WHERE column_name IS NULL;

Exam-relevant note: Remember that IN means “matches at least one value,” NOT IN means “matches none of the listed values” only when NULL does not interfere, and IS NULL is required for testing missing values.

For a concise rule to remember: use IN for a known set of acceptable values, use a subquery when that set comes from table data, and check NULL behavior whenever you use NOT IN.