Joe Celko's SQL for Smarties, 3rd Edition

14.5. IN() Predicate and Scalar Queries

As mentioned before, the list of an IN() predicate can be any scalar expression. This includes scalar subqueries, but most people do not seem to know that this is possible. For example, given tables that model warehouses, trucking centers, and so forth, we can find if we have a product, identified by its UPC code, somewhere in the enterprise.

SELECT P.upc
  FROM Picklist AS P
 WHERE P.upc
       IN ((SELECT upc FROM Warehouse AS W WHERE W.upc =
Picklist.upc),
           (SELECT upc FROM TruckCenter AS T WHERE T.upc =
Picklist.upc),
         ...
           (SELECT upc FROM Garbage AS G WHERE G.upc =
Picklist.upc));

The empty result sets will become NULLs in the list. The alternative to this is usually a chain of OUTER JOINs or an ORed list of ...