What is the SQL IN operator used for?

The SQL IN operator filters rows where a column's value matches any value in a specified list. It is a shorthand for multiple OR conditions: WHERE col IN ('A','B') is equivalent to WHERE col = 'A' OR col = 'B'.

Can SQL IN be used with a subquery?

Yes. The IN operator can take a subquery instead of a literal list: WHERE column_name IN (SELECT column FROM another_table WHERE condition). The subquery must return exactly one column.

What is the difference between IN and BETWEEN in SQL?

IN is used for a discrete list of values (e.g., specific store names or IDs). BETWEEN is used for a continuous range with a defined minimum and maximum. You cannot use IN to express a range like 'all values between 0 and 1'.

How do I use NOT IN in SQL?

NOT IN excludes rows matching any value in the list: WHERE col NOT IN ('A','B'). Be careful with NULL values — if any value in the list is NULL, NOT IN may return no rows because NULL comparisons are indeterminate.

SQL IN Operator | Filter by a List of Values

SQL > SQL Commands > In

The IN operator in SQL filters the result set based on a list of discrete values. The list of discrete values can be simply be listed out or is provided by a separate SELECT statement (this is called a subquery).

The SQL IN operator is a concise alternative to multiple OR conditions — it checks whether a column's value matches any item in a list or subquery result, and is always used with the WHERE clause.

The IN operator is always used with the WHERE clause.

Syntax

Below is the syntax for the IN operator when the possible values are listed out directly.

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

The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or string characters. If there is only one value inside the parenthesis, this commend is equivalent to,

WHERE "column_name" = 'value1'

The syntax for the IN operator in a subquery construct is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ( [SELECT STATEMENT] );

In the above construct, the subquery statement must retrieve only one column, as the values returned from that column is what will be used to filter the results. If two or more columns are selected in the subquery, an error will result.

Please note that the IN operator cannot be used if the filtering criteria is a continuous range. For example, if we are looking for any value that is between 0 and 1, we cannot use the IN operator because it is not possible to list every possible value between 0 and 1.

Example

We use the following table for our example.

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

To select all records for the Los Angeles and the San Diego stores in Table Store_Information, we key in,

SELECT *
FROM Store_Information
WHERE Store_Name IN ('Los Angeles', 'San Diego');

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999

For an example of the IN operator used in conjunction with a separate SELECT statement, please see the Subquery section.

Frequently Asked Questions

Is SQL IN faster than using multiple OR conditions?

In most modern databases, the query optimizer treats IN and equivalent OR conditions the same way. However, IN is generally more readable and easier to maintain, especially when the list has many values.

Can the IN list contain NULL values?

Including NULL in an IN list does not cause errors, but a NULL comparison is always indeterminate. A row where the column is NULL will not match NULL in the list. Use IS NULL separately to check for null values.

What is the maximum number of values in an IN clause?

Limits vary by database. Oracle has a limit of 1000 values per IN list. MySQL and PostgreSQL have much higher practical limits. For very large value sets, consider using a temporary table with a JOIN instead.

How does NOT IN behave with NULL values in the list?

If the IN list (or subquery) contains any NULL, NOT IN returns no rows at all because the comparison with NULL is UNKNOWN, and UNKNOWN propagates to make the whole condition false. Use NOT EXISTS as a safer alternative when NULLs may be present.

Exercises

For these exercises, assume we have a table called Users with the following data:

Table Users

First_Name	Last_Name	Birth_Date	Gender	Join_Date
Sophie	Lee	Jan-05-1960	F	Apr-05-2015
Richard	Brown	Jan-07-1975	M	Apr-05-2015
Jamal	Santo	Oct-08-1983	M	Apr-09-2015
Casey	Healy	Sep-20-1969	M	Apr-09-2015
Jill	Wilkes	Nov-20-1979	F	Apr-15-2015

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT Gender FROM Users IN ('M');
b) SELECT * FROM Users HAVING Gender IN ('M','F');
c) SELECT First_Name, Last_Name FROM Users WHERE Gender IN ('M','F');
d) SELECT DISTINCT First_Name, Last_Name WHERE Gender IN ('M','F');

2. How many records will be returned by the following query?
SELECT * FROM Users WHERE Join_Date IN ('Apr-05-2015','Apr-15-2015');

3. How many records will be returned by the following query?
SELET * FROM Users WHERE Gender IN ('M') AND Join_Date = 'Apr-15-2015');

Next: SQL BETWEEN

This page was last updated on March 19, 2026.

SQL IN