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.

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

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