IN condition

An IN condition is a membership condition. It checks whether a value is a member of a list of values or subqueries.

Syntax

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

Usage instructions

IN conditions mainly have the following two forms:

• IN indicates that the value is a member of the set. 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 indicates that the value is not a member of the set. If any member in the set is NULL, the result is FALSE or UNKNOWN. 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;