Where IN
WHERE IN returns values that matches values in a list or the results of a subquery.
SQL Where IN Syntax:
SELECT Column1, Column2, Column3
FROM Table_Name
WHERE Column1 IN ( Value1, Value2, Value3)
Example:
Lets use the “Orders” table to find all Orders with the ProductID of two, four, and five.
| OrderID | ProductID | EmployeeID | OrderDate | CustomerID |
|---|---|---|---|---|
| 10 | 2 | 1 | 9/9/2009 | 2 |
| 11 | 3 | 2 | 1/9/2009 | 3 |
| 12 | 4 | 1 | 3/9/2009 | 4 |
| 13 | 1 | 3 | 9/9/2010 | 5 |
| 14 | 3 | 1 | 9/30/2009 | 3 |
| 15 | 5 | 2 | 6/12/2009 | 1 |
| 16 | 3 | 4 | 4/9/2009 | 6 |
SELECT
OrderID,
ProductID,
EmployeeID,
OrderDate,
CustomerID
FROM Orders
WHERE ProductID IN( 2, 4, 5)
Results:
| OrderID | ProductID | EmployeeID | OrderDate | CustomerID |
|---|---|---|---|---|
| 10 | 2 | 1 | 9/9/2009 | 2 |
| 12 | 4 | 1 | 3/9/2009 | 4 |
| 15 | 5 | 2 | 6/12/2009 | 1 |
Lets find all orders with an OrderID greater than 13 using a subquery for the filter.
SELECT
OrderID,
ProductID,
EmployeeID,
OrderDate,
CustomerID
FROM Orders
WHERE OrderID IN( Select
OrderID
FROM
WHERE OrderID >13)
Results:
| OrderID | ProductID | EmployeeID | OrderDate | CustomerID |
|---|---|---|---|---|
| 14 | 3 | 1 | 9/30/2009 | 3 |
| 15 | 5 | 2 | 6/12/2009 | 1 |
| 16 | 3 | 4 | 4/9/2009 | 6 |
SQL Tutorial