SQL WHERE IN

WHERE IN returns values that match values in a list.

This list is either hardcoded or generated by a subquery.

WHERE IN is shorthand for multiple OR conditions.

Example

#

List all customers from London or Paris.

SELECT *
  FROM Customer
 WHERE City IN ('Paris','London')
Result:  8 records
Id FirstName LastName City Country Phone
4 Thomas Hardy London UK (171) 555-7788
11 Victoria Ashworth London UK (171) 555-1212
16 Elizabeth Brown London UK (171) 555-2282
19 Ann Devon London UK (171) 555-0297
53 Simon Crowther London UK (171) 555-7733
57 Marie Bertrand Paris France (1) 42.34.22.66
72 Hari Kumar London UK (171) 555-1717
74 Dominique Perrier Paris France (1) 47.55.60.10

Syntax

WHERE IN syntax.

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

More Examples

WHERE IN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
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

WHERE NOT IN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products that are not priced at $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

WHERE IN Subquery

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

You may also like



Guides


vsn 3.1