IN and NOT IN
The IN operator is used to determine whether a given value matches any value in a list of target values. The list of target values can be generated using a subquery.
The IN operator has two formats. One format uses an expression; the other uses a subquery.
Syntax 1
<source-expression [, source-expression]>
[NOT] IN <scalar-expression-list>
Syntax 2
<source-expression [, source-expression]>
[NOT] IN <subquery [, subquery]>
Remarks
• IN is a comparison operator like < (less than) or LIKE.
• IN is valid anywhere a conditional expression can be used. That is, you can place IN in a WHERE clause, a HAVING clause, or a JOIN ON clause, as well as in a CASE expression.
• All the expressions in the target list (<scalar-expression-list>) must be compatible or implicitly castable to the source expression (<source-expression>), or vice versa.
• If the items in the target list are not all of the same type, as in the following example:
ID IN (1000, ‘X’, 12.0)
the list is translated to the following format:
(left = right1) OR (left = right2) OR (left = right3)
with CASE functions as necessary.
• You can use IN with data types that are comparable or implicitly castable to each other.
• You can combine IN conditions with AND and OR conditions.
• The expression A IN (B, C) is equivalent to the expression A = B or A = C.
• You can use NOT IN to negate the IN condition. That is, NOT IN specifies values that are not in the target list.
• The subquery can return only one column of a compatible data type. However, it can return multiple rows.
• The subquery is run once prior to running the parent query, to populate the list of values for the IN clause.
• You can combine IN conditions using AND and OR conditions.
• IN can take multiple source (left-side) expressions, and multiple values in the subquery. However, the number of values on the right side must match the number of values on the left side.
• Multiple sets of values are allowed.
Example (Syntax 1, Using IN with a String)
SELECT customers.CompanyName, customers.StateOrProvince
FROM /shared/examples/ds_orders/customers customers
WHERE StateOrProvince IN (‘CA’, ‘PA’)
Example (Syntax 1, Using IN with a Number)
SELECT ProductId, ProductName
FROM /shared/examples/ds_inventory/products
WHERE CategoryID IN (5,6)
Example (Syntax 1, Using IN with Date)
SELECT purchaseorders.ShipDate, SupplierID
FROM /shared/examples/ds_inventory/purchaseorders PurchaseOrders
WHERE ShipDate IN (CAST ('2003-02-06' AS DATE), CAST ('2003-02-07' AS DATE) )
Example (Syntax 1, Using IN with AND and OR)
SELECT purchaseorders.ShipDate, SupplierID
FROM /shared/examples/ds_inventory/purchaseorders PurchaseOrders
WHERE ShipDate IN (TO_DATE (‘2003-02-06’))
AND ShippingMethodID = 3
OR DatePromised = ‘2003-02-02’
OR ShipDate IN (‘2001-05-08’, DATE ‘2001-04-01’, ‘2000-02-25’)
Example (Syntax 2, Using IN)
SELECT Customers.ContactName
FROM /shared/examples/ds_orders/Customers Customers
WHERE City IN (SELECT City
FROM /shared/examples/ds_orders/Customers Customers
WHERE City = ‘New York’)
Example (Syntax 2, Using NOT IN)
SELECT Customers.ContactName, CompanyName
FROM /shared/examples/ds_orders/Customers Customers
WHERE City
NOT IN (SELECT City
FROM /shared/examples/ds_orders/Customers Customers
WHERE City = ‘New York’)