IN Subquery
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.
IN Subquery
Notes:
- 1 See Expression
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| subquery | Embedded query | Cannot contain the FIRST nor the ORDER BY clause | SELECT statement |
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.