Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.