메뉴 닫기

SQL WHERE IN Clause

The SQL WHERE IN syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE columnname 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

댓글 남기기

이메일은 공개되지 않습니다.