User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Testing set membership with IN conditions

You can use the subquery set membership test to compare a value from the main query to more than one value in the subquery.

The subquery set membership test compares a single data value for each row in the main query to the single column of data values produced by the subquery. If the data value from the main query matches one of the data values in the column, the subquery returns TRUE.

Example 

Select the names of the employees who head the Shipping or Finance departments:

SELECT emp_fname, emp_lname
FROM employee 
WHERE emp_id IN (
   SELECT dept_head_id 
   FROM department 
   WHERE (dept_name='Finance' or dept_name = 'Shipping'))

emp_fname	emp_lname
David	Scott
Jose	Martinez

The subquery in this example

SELECT dept_head_id 
FROM department 
WHERE (dept_name='Finance' OR dept_name = 'Shipping')

extracts from the department table the id numbers that correspond to the heads of the Shipping and Finance departments. The main query then returns the names of the employees whose id numbers match one of the two found by the subquery.

Negation of the set membership test 

The subquery set membership test can also be used to extract those rows whose column values are not equal to any of those produced by a subquery. To negate a set membership test, insert the word NOT in front of the keyword IN.

Example 

The subquery in this query returns the first and last names of the employees that are not heads of the Finance or Shipping departments.

SELECT emp_fname, emp_lname
FROM employee 
WHERE emp_id NOT IN (
   SELECT dept_head_id 
   FROM department 
   WHERE (dept_name='Finance' OR dept_name = 'Shipping'))