SQL WHERE IN Clause

SQL WHERE IN Clause
WHERE IN returns values that matches values in a list or subquery.
WHERE IN is a shorthand for multiple OR conditions.
The definitive guide
for data professionals




The SQL WHERE IN syntax

The general syntax is:
SELECT column-names
  FROM table-name
 WHERE column-name IN (values)


PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

SQL WHERE IN Examples



Problem: List all suppliers from the USA, UK, OR Japan
SELECT Id, CompanyName, City, Country
  FROM Supplier
 WHERE Country IN ('USA', 'UK', 'Japan')


Results: 8 records.



Id CompanyName City Country
1 Exotic Liquids London UK
2 New Orleans Cajun Delights New Orleans USA
3 Grandma Kelly's Homestead Ann Arbor USA
4 Tokyo Traders Tokyo Japan
6 Mayumi's Osaka Japan
8 Specialty Biscuits, Ltd. Manchester UK
16 Bigfoot Breweries Bend USA
19 New England Seafood Cannery Boston USA

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued


Problem: List all products that are not exactly $10, $20, $30, $40, or $50
SELECT Id, ProductName, UnitPrice
  FROM Product
 WHERE UnitPrice NOT IN (10,20,30,40,50)


Results: 72 records.



Id ProductName UnitPrice
1 Chai 18.00
2 Chang 19.00
4 Chef Anton's Cajun Seasoning 22.00
5 Chef Anton's Gumbo Mix 21.35
6 Grandma's Boysenberry Spread 25.00



SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
CUSTOMER
Id
FirstName
LastName
City
Country
Phone


Problem: List all customers that are from
the same countries as the suppliers.
SELECT Id, FirstName, LastName, Country
  FROM Customer
 WHERE Country IN
       (SELECT Country
          FROM Supplier)


Results: 91 records.



Id FirstName LastName Country
1 Maria Anders Germany
4 Thomas Hardy UK
5 Christina Berglund Sweden
6 Hanna Moos Germany
7 Frédérique Citeaux France