IN Conditional Expression

Description

The IN conditional expression compares to determine whether the single data value on the left is included in the list specified on the right. That is, the predicate returns TRUE if the single data value on the left is an element of the expression specified on the right. If NOT comes before the IN keyword, the result of a NOT operation on the result of the IN operation is returned.

Syntax

expression [ NOT ] IN expression

Example

--selecting rows where department is sales or devel

SELECT * FROM condition_tbl WHERE dept_name IN {'devel','sales'};

SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};

           id  name                  dept_name                  salary

======================================================================

            1  'Kim       '          'devel'                   4000000

            2  'Moy       '          'sales'                   3000000

            3  'Jones     '          'sales'                   5400000

            4  'Smith     '          'devel'                   5500000

            6  'Smith     '          'devel'                   2400000

 

--selecting rows where department is neither sales nor devel

SELECT * FROM condition_tbl WHERE dept_name NOT IN {'devel','sales'};

           id  name                  dept_name                  salary

======================================================================

            5  'Kim       '          'account'                 3800000

            7  'Brown     '          'account'                    NULL