DB2 - SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

IN operator accomplishes the same goal as OR. But IN has the following advantages.

• For long lists of valid options, the IN operator syntax is far cleaner and easier to read.

• The order of evaluation is easier to manage.

• The biggest advantage of IN is that the IN operator can contain another SELECT statement.

Syntax:

SELECT column_name(s)
  FROM table_name
  WHERE column_name [NOT] IN (value1, value2, ...);

or:

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

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Using the IN Operator:

The following SQL statement selects all the products with the Category as "Accessories", "Components" and "Bikes".

SELECT *
    FROM Product
    WHERE Category IN ('Accessories', 'Components', 'Bikes');

Result:

Example 2: Using the NOT Operator

NOT is a keyword used in a WHERE clause to negate a condition.

The following SQL statement selects all the products that are not the Category as "Accessories", "Components" and "Bikes".

Example:

SELECT *
  FROM Product
  WHERE Category NOT IN ('Accessories', 'Components', 'Bikes');

Result:

Example 3: Using IN Operator with SELECT

The following SQL statement selects all students that are from the same countries as the Teachers:

Example:

SELECT * FROM Students
    WHERE Country IN (SELECT Country FROM Teachers);

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!