The SQL WHERE IN syntax

The general syntax is:

1. SELECT column–names
2. FROM table–name
3. WHERE column–name IN (values)

SQL WHERE IN Examples

Problem: List all suppliers from the USA, UK, OR Japan

1. SELECT Id, CompanyName, City, Country
2. FROM Supplier
3. WHERE Country IN (‘USA’, ‘UK’, ‘Japan’)

Results: 8 records.

Problem: List all products that are not exactly $10, $20, $30, $40, or $50

1. SELECT Id, ProductName, UnitPrice
2. FROM Product
3. 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.

1. SELECT Id, FirstName, LastName, Country
2. FROM Customer
3. WHERE Country IN
4. (SELECT Country
5. 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