IN condition

An IN condition is a membership condition. It tests whether a value or a subquery result is a member of a list of values or a subquery result.

The syntax of an IN condition is as follows:

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

The IN condition tests whether an expression is a member of an expression list or a subquery result, or whether multiple expressions are members of an expression list or a subquery result. Each expression in the expression list must match the expression on the left side of the IN operator in number and data type. You can specify more than 1000 expressions in the expression_list clause. An IN condition can be in one of the following two forms:

• IN is equivalent to =ANY, and indicates that all members of the set are to be tested. Here is an example:

  SELECT * FROM emp WHERE job_id IN ('PU_CLERK','SH_CLERK') ORDER BY emp_id;
  
  SELECT * FROM emp WHERE salary IN (SELECT salary  FROM emp WHERE dept_id =30)
   ORDER BY emp_id;
  

• NOT IN is equivalent to != ALL. If any member of the set is NULL, the result is FALSE. Here is an example:

  SELECT * FROM emp WHERE salary NOT IN (SELECT salary FROM emp WHERE dept_id = 30)
     ORDER BY emp_id;
  
  SELECT * FROM emp WHERE job_id NOT IN ('PU_CLERK', 'SH_CLERK') ORDER BY emp_id;
  

  If any item in the list following the NOT IN operator evaluates to NULL, the result for all rows is FALSE or UNKNOWN, and no rows are returned.