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')
Try it live
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
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')
Try it live
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 priced at $10, $20, $30, $40, or $50.
SELECT Id, ProductName, UnitPrice
FROM Product
WHERE UnitPrice NOT IN (10,20,30,40,50)
Try it live
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)
Try it live
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