SQL WHERE IN Clause

What does SQL IN return?

WHERE IN returns values that matches values in a list or subquery.
The WHERE IN clause is shorthand for multiple OR conditions.

The SQL WHERE IN syntax

The general syntax is

SELECT column-names
  FROM table-name
 WHERE column-name IN (values) 

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

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')
Result:  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)
Result:  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 where the suppliers are.
SELECT Id, FirstName, LastName, Country
  FROM Customer
 WHERE Country IN 
       (SELECT Country 
          FROM Supplier) 
Result:  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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas