SQL IN Operator Explained with Practical Examples

A filter that has to accept four different values, written with OR, repeats the column name four times, and each value you add repeats it once more. IN writes the column once and the accepted values after it, inside parentheses. A row is returned when the column equals any value in that list, and NOT IN returns the rows that match none of them.

What the SQL IN operator does

IN belongs in the WHERE clause, directly after the column name it tests. Every query on this page runs against one table of four students:

CREATE TABLE Students (
    ID    INT PRIMARY KEY,
    Name  VARCHAR(50),
    Age   INT,
    Grade CHAR(1)
);

INSERT INTO Students VALUES
    (1, 'Alice',   23, 'A'),
    (2, 'Bob',     25, 'B'),
    (3, 'Charlie', 23, 'A'),
    (4, 'David',   24, 'C');

The students whose grade is A or B:

SELECT Name
FROM Students
WHERE Grade IN ('A', 'B');

Three of the four rows come back. David's grade is C, which is not one of the listed values, so his row is dropped. The list can be as long as you need, it always goes in parentheses, and the values are separated by commas.

IN compared with the = operator

The same filter written without IN is a chain of OR conditions, one per value:

SELECT Name
FROM Students
WHERE Grade = 'A' OR Grade = 'B';

Both queries return the same three rows, so the choice between them is about the person reading the query later. The = operator takes exactly one value on its right, and the OR chain repeats the column name for every extra value it has to accept. IN names the column once, which is why a list of six product codes reads as a list of six product codes rather than as six conditions.

Write = when there is one value to match, because the shorter form says exactly that. Write IN from the second value onwards. The one case where = still wins with several values is when each condition tests a different column, since IN compares one column against a list of values, never several columns against one value.

The values an IN list can hold

Every value in the list is compared against the column, so all of them have to be of the column's type. Strings, numbers and dates are each written the way that type is written anywhere else in SQL.

IN with strings

Each string value carries its own pair of single quotes:

SELECT Name
FROM Students
WHERE Name IN ('Alice', 'David');

IN with numbers

Numbers are written without quotes:

SELECT Name
FROM Students
WHERE ID IN (1, 4);

IN with dates

A date is written as a quoted literal in the format the engine reads, and it is compared against a date column. A second table holds three orders:

CREATE TABLE Orders (
    OrderID   INT PRIMARY KEY,
    Product   VARCHAR(50),
    OrderDate DATE
);

INSERT INTO Orders VALUES
    (1, 'Apple',  '2023-01-10'),
    (2, 'Banana', '2023-02-15'),
    (3, 'Cherry', '2023-01-10');

The products ordered on either of two days:

SELECT Product
FROM Orders
WHERE OrderDate IN ('2023-01-10', '2023-02-15');

Both dates match rows, so all three orders come back. Drop the second date from the list and the result is Apple and Cherry, the two orders placed on 10 January.

IN with a subquery

The list does not have to be typed out. A subquery that returns a single column can stand in for it, and the engine compares the column on the left against every value that subquery produced. The students of the same age as Alice, without you needing to know what that age is:

SELECT Name
FROM Students
WHERE Age IN (SELECT Age FROM Students WHERE Name = 'Alice');

The subquery returns one age, 23, and IN matches every student who has it: Alice herself, and Charlie. A subquery that returns more than one column is rejected, because IN has one column on its left and needs one on its right.

NOT IN, and what a NULL does to it

NOT IN keeps the rows that match none of the values:

SELECT Name
FROM Students
WHERE Name NOT IN ('Alice', 'David');

A subquery works on this side too. The students whose grade is not A:

SELECT Name
FROM Students
WHERE Grade NOT IN (SELECT Grade FROM Students WHERE Grade = 'A');

One value breaks the symmetry between the two operators. Comparing anything against NULL produces UNKNOWN rather than true or false, which the SQL Server documentation states for IN and NOT IN by name. A WHERE clause keeps only the rows whose condition came out true, so a NULL anywhere in a NOT IN list leaves no row that can qualify:

SELECT Name
FROM Students
WHERE Grade NOT IN ('A', NULL);

The query returns no rows, not the three students whose grade is not A. IN is not affected the same way, because a row that equals one of the other values is still true. When the list comes from a subquery, add IS NOT NULL to that subquery's own WHERE clause and the trap is closed.

IN in an UPDATE statement

IN is a condition, not a part of SELECT, so it picks the rows an UPDATE changes and the rows a DELETE removes in exactly the same way:

UPDATE Students
SET Grade = 'D'
WHERE Name IN ('Alice', 'David');

Reading the table back shows which rows moved:

SELECT Name, Grade
FROM Students;

Two rows changed and two were left alone. An UPDATE gives back a count of the rows it changed, not the rows themselves, so run the same WHERE clause as a SELECT first and read what comes back.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Practice IN and NOT IN in DbSchema

DbSchema connects to your database, reverse-engineers it into a diagram, and opens a SQL Editor over the same connection. Type the SELECT with the IN list, press Execute Query, and DbSchema puts the rows that matched in the result pane below the statement. Once that list of rows is the one you meant, change SELECT to UPDATE and run it again.

Both statements run against the connected database rather than against the diagram, and DbSchema keeps an UPDATE pending until you press Commit or Rollback in the SQL Editor toolbar, so a value list that turns out to be wrong can still be discarded. Connecting, reverse-engineering the schema and the SQL Editor are all in the free Community Edition.

Common mistakes with IN

Leaving out the parentheses is the mistake the engine catches for you: the statement is rejected before it runs. Mixing types inside the list is the one it does not catch. Quoted values compared against a numeric column force the engine to convert one side before it can compare them, and the answer then depends on that conversion rather than on the value you wrote, so keep every value in the list the same type as the column.

Length is the third. SQL Server's documentation warns that many thousands of values written out between the parentheses can consume resources and return error 8623 or 8632, and gives the fix: store the values in a table and put a subquery in the IN clause instead.

Practice questions

1. Write a query to find products not sold on 2023-02-15.
2. Update student grades to F for ages 23 and 25.
3. Fetch students with grades other than A and B.
4. Retrieve orders placed on 2023-01-10 and 2023-02-15.

Each one is a value list waiting for a table to run against. Download DbSchema at https://dbschema.com/download.html, connect it to your own database, and paste the queries into the SQL Editor, which comes with the free Community Edition along with connecting and reverse-engineering the schema.

Frequently asked questions

Is IN case-sensitive?

The collation of the compared values decides, not the IN operator. MySQL 8.4 documents its default collation utf8mb4_0900_ai_ci as case-insensitive for non-binary strings, so a list holding 'alice' matches a stored Alice; a case-sensitive collation such as utf8mb4_bin does not.

Can IN be used with JOIN?

IN is a condition in the WHERE clause, and a joined query has the same WHERE clause as any other query, so the list can test a column from either table.

Is there a limit to how many values IN accepts?

The engine decides. Oracle publishes a figure for it: in Oracle AI Database 26ai, a comma-delimited list of expressions can contain no more than 65,535 expressions, and the values written between the parentheses of an IN condition are one of those lists.