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


Order today!
 See 2 min video



The SQL WHERE IN syntax


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




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





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





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