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

SELECT GivenName, Surname
FROM Employees
WHERE EmployeeID IN (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );

The subquery in this example extracts from the Departments 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.

SELECT DepartmentHeadID
FROM Departments
WHERE ( DepartmentName='Finance' OR 
       DepartmentName = 'Shipping' );

The subquery set membership test is equivalent to the =ANY test. The following query is equivalent to the query from the above example.

SELECT GivenName, Surname
FROM Employees
WHERE EmployeeID = ANY (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );

You can also use the subquery set membership test 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.

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 GivenName, Surname
FROM Employees
WHERE EmployeeID NOT IN (
   SELECT DepartmentHeadID
   FROM Departments
   WHERE ( DepartmentName='Finance' OR 
          DepartmentName = 'Shipping' ) );