IN condition

The IN condition is a membership condition. It is used to test a value for membership in a list of values or subquery.

The syntax of the IN condition is as follows:

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

The IN condition can test whether one or multiple expressions are members of an expression list or a subquery. The expressions in each expression list must match the expressions to the left of the IN operator in both number and data type. You can specify a maximum of 1,000 expressions in expression_list. The IN condition can be used in two forms.

• IN is equivalent to =ANY, indicating all members in the set. 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 in the set is NULL, the calculation 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 the calculation result of any item in the list following the NOT IN operation is NULL, the calculation results of all rows are FALSE or UNKNOWN, and no row is returned.