IN Subquery

IN Subquery:
 
                  (1)
|--| Expression |-------+-----+--IN--(--subquery--)-------------|
                        '-NOT-'
 

1. See page Expression

An IN subquery condition is TRUE if the value of the expression matches one or more of the values from the subquery. (The subquery must return only one row, but it can return more than one column.) The keyword IN is equivalent to the =ANY specification. The keywords NOT IN are equivalent to the !=ALL specification. See the ALL, ANY, and SOME Subqueries.

The following example of an IN subquery finds the order numbers for orders that do not include baseball gloves (stock_num = 1):

WHERE order_num NOT IN
   (SELECT order_num FROM items WHERE stock_num = 1)

Because the IN subquery tests for the presence of rows, duplicate rows in the subquery results do not affect the results of the main query. Therefore, the UNIQUE or DISTINCT keyword in the subquery has no effect on the query results, although not testing duplicates can improve query performance.