SQL IN and NOT IN

Use IN when a column can match any value from a list. It is often clearer than repeating several equality conditions with OR.

SELECT id, name, class, mark
FROM student
WHERE class IN (
    'Four',
    'Fifth'
);

This returns students whose class is either Four or Fifth.

SQL IN Syntax Top ↑

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

The list can contain text, numbers, dates or values returned by a one-column subquery, as long as they are comparable with the left-hand expression.

IN vs Repeated OR Conditions Top ↑

This query:

SELECT id, name, class
FROM student
WHERE class = 'Four'
   OR class = 'Fifth'
   OR class = 'Six';

can be written more compactly as:

SELECT id, name, class
FROM student
WHERE class IN (
    'Four',
    'Fifth',
    'Six'
);

Use OR when the alternatives involve different columns or different types of conditions. Use IN when several alternatives are equality checks against the same expression.

IN with Numeric Values Top ↑

Numeric values do not need quotes:

SELECT id, name, class, mark
FROM student
WHERE id IN (
    1,
    2,
    4,
    7
);

This returns only the listed student IDs.

SQL NOT IN Top ↑

NOT IN excludes values in the list.

SELECT id, name, class, mark
FROM student
WHERE class NOT IN (
    'Four',
    'Fifth'
);

With the sample rows shown above, this leaves the class Three records.

IN with a Subquery Top ↑

The IN list can come from another SELECT query. Suppose student_sports contains the IDs of students who joined sports:

SELECT id, name, class, mark
FROM student
WHERE id IN (
    SELECT id
    FROM student_sports
);

The inner query returns one column of IDs. The outer query then returns the full student records for those IDs.

See SQL subqueries for more examples.

NOT IN with Subqueries and NULL Top ↑

NOT IN needs extra care when the list or subquery can contain NULL.

For example:

SELECT id, name
FROM student
WHERE id NOT IN (
    SELECT id
    FROM student_sports
);

If the subquery can return NULL, SQL's three-valued logic can make the NOT IN result unknown and prevent rows from matching as expected.

If NULLs are possible, either exclude them explicitly:

SELECT id, name
FROM student
WHERE id NOT IN (
    SELECT id
    FROM student_sports
    WHERE id IS NOT NULL
);

or consider a correlated NOT EXISTS design when that better expresses the anti-match requirement.

See SQL NULL values for the underlying comparison behavior.

Dynamic IN List with PHP PDO Top ↑

A PDO placeholder represents one data value. You cannot bind one array directly to a single placeholder and expect it to expand into an IN list.

This does not work as intended:

<?php
$ids=[1,4,7];

$stmt=$dbo->prepare(
    "SELECT id,name
     FROM student
     WHERE id IN (:ids)"
);

Create one placeholder for each validated value instead:

<?php
require 'config.php';

$ids=[1,4,7];

$ids=array_values(
    array_filter(
        $ids,
        fn($id) => filter_var(
            $id,
            FILTER_VALIDATE_INT,
            ['options' => ['min_range' => 1]]
        ) !== false
    )
);

if($ids===[]){
    exit('No valid IDs supplied.');
}

$placeholders=implode(
    ',',
    array_fill(
        0,
        count($ids),
        '?'
    )
);

$sql="SELECT id,name,class,mark
      FROM student
      WHERE id IN ($placeholders)
      ORDER BY id";

$stmt=$dbo->prepare($sql);

foreach($ids as $position => $id){
    $stmt->bindValue(
        $position+1,
        (int)$id,
        PDO::PARAM_INT
    );
}

$stmt->execute();

The SQL structure is generated only from application-controlled placeholder characters. The actual IDs remain bound data values.

IN with Aggregate Queries Top ↑

IN can filter the rows used by aggregate functions such as SUM():

SELECT SUM(mark) AS total_mark
FROM student
WHERE id IN (
    1,
    2,
    3,
    4
);

The aggregate is calculated only from rows whose IDs are in the list.

Handling an Empty IN List Top ↑

Application code sometimes builds an IN list from selected checkboxes, filters or IDs returned by another process. If there are no values, do not generate:

-- Invalid normal IN-list syntax
WHERE id IN ()

Instead, decide what an empty selection should mean:

• return no rows,
• skip the filter and return all permitted rows, or
• stop and ask the user to select at least one value.

That decision belongs in the application logic rather than being hidden inside an invalid SQL statement.

IN Performance Notes Top ↑

• Small literal IN lists are usually straightforward for MySQL to optimize.
• An index on the compared column can help, depending on the query and data distribution.
• For very large lists, consider whether the values should instead live in a table and be joined or queried through a subquery.
• For IN subqueries, performance depends on the query plan, indexes and the size of both result sets.
• Use EXPLAIN when a production IN query is unexpectedly slow.

IN vs FIND_IN_SET() Top ↑

IN compares one expression against a list of separate SQL values:

WHERE class IN (
    'Four',
    'Fifth'
)

FIND_IN_SET() searches inside a comma-separated string value:

WHERE FIND_IN_SET(
    'Four',
    class_list
) > 0

These are different data models. If a field stores multiple independent values in one comma-separated column, consider whether a normalized related table would be a better schema.

Common SQL IN Mistakes Top ↑

Using IN for unrelated conditions Top ↑

IN is best when one expression is compared against several candidate values. Use AND/OR when the conditions involve different columns or operators.

Returning multiple columns from a single-column IN subquery Top ↑

For id IN (subquery), the subquery should return one compatible column.

Binding an array to one PDO placeholder Top ↑

Create one placeholder per value. PDO does not expand one bound array into a comma-separated SQL list.

Ignoring NULL with NOT IN Top ↑

A NULL in the compared list or subquery can make NOT IN behave differently from what beginners expect because the result can become unknown.

Generating IN () from an empty application array Top ↑

Handle the empty-list meaning in application logic before building the SQL.

Putting numeric data in quotes by habit Top ↑

Use values according to their actual datatype. Prepared statements make this clearer by binding integers as integers and strings as strings.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: