Applying the IN and NOT IN operators

The IN and NOT IN operators are used to compare a value against a list of values or the result of a subquery. These operators allow you to perform filtering based on whether a value matches any value in the specified list or subquery.

Table of Contents

IN Operator

The IN operator checks if a value matches any value in a list or a result set returned by a subquery. If the value matches at least one value in the list or subquery, the IN operator evaluates to true; otherwise, it evaluates to false.

SELECT column1, column2
FROM table1
WHERE column1 IN ('value1', 'value2', 'value3');

The IN operator checks if the value of “column1” in “table1” matches any of the values provided in the list. If a match is found, the corresponding row is included in the result set.

You can also use a subquery with the IN operator to compare a value against the result set of the subquery.

SELECT column1, column2
FROM table1
WHERE column1 IN (
    SELECT column1
    FROM table2
    WHERE condition
);

NOT IN Operator

The NOT IN operator is the negation of the IN operator. It checks if a value does not match any value in a list or the result set of a subquery. If the value does not match any value in the list or subquery, the NOT IN operator evaluates to true; otherwise, it evaluates to false.

SELECT column1, column2
FROM table1
WHERE column1 NOT IN ('value1', 'value2', 'value3');

In this example, the NOT IN operator checks if the value of “column1” in “table1” does not match any of the values provided in the list. If no match is found, the corresponding row is included in the result set.

Similar to the IN operator, you can use a subquery with the NOT IN operator to compare a value against the result set of the subquery.

SELECT column1, column2
FROM table1
WHERE column1 NOT IN (
    SELECT column1
    FROM table2
    WHERE condition
);

The IN and NOT IN operators provide a concise way to filter data based on whether a value matches any value in a list or subquery. They are particularly useful when you have a predetermined set of values or want to compare values across multiple tables using subqueries.