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;