All Products
Search
Document Center

IN conditions

Last Updated: Jun 18, 2021

An IN condition is a membership condition. It tests a value or a member value that is in a subquery list.

Syntax

expr [ NOT ] IN  ({ expression_list | subquery }) 
|
( expr [, expr ]... ) [ NOT ]  IN  ( { expression_list [, expression_list ]... | subquery } )

An IN condition tests whether an expression is a member of an expression list or a subquery, or whether multiple expressions are members of expression lists or subqueries. The expressions in each expression list must match the expressions to the left of the IN operator in terms of quantity and data types.

Examples

IN example: equivalent to =ANY. It indicates all the members in the set.

SELECT * FROM employees WHERE job_id IN ('PU_CLERK','SH_CLERK') ORDER BY employee_id;

SELECT * FROM employees WHERE salary IN (SELECT salary  FROM employees 
WHERE department_id =30) ORDER BY employee_id;

NOT IN example: equivalent to ! = ALL. If a member in the set is NULL, the calculation result is false.

SELECT * FROM employees WHERE salary NOT IN (SELECT salary FROM employees 
WHERE department_id = 30) ORDER BY employee_id;

SELECT * FROM employees WHERE job_id NOT IN ('PU_CLERK', 'SH_CLERK')
ORDER BY employee_id;