A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator:

SQL> SELECT first_name, last_name FROM employees

2 WHERE department_id IN (

3 SELECT department_id FROM departments

4 WHERE location_id = 1700

5 );

FIRST_NAME   LAST_NAME

-------------------- -------------------------

Steven   King

Neena   Kochhar

Lex   De Haan

Nancy   Greenberg

Daniel   Faviet

John    Chen

Ismael   Sciarra

......