Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

SQL IN

The SQL IN Operator

The IN operator, utilized within a WHERE clause, enables specification of multiple values.

It functions as a shorthand for multiple OR conditions.

Example

Retrieve all customers from ‘Germany’, ‘France’, or ‘UK’.

SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);'

Demo Database

Here is a sample from the Customers table utilized in the examples:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

NOT IN

By placing the NOT keyword in front of the IN operator, you retrieve all records that are not any of the values in the list.

Example

Retrieve all customers who are not from ‘Germany’, ‘France’, or ‘UK’.

SELECT * FROM Customers
WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);

IN (SELECT)

You can also employ the IN operator with a subquery in the WHERE clause.

Using a subquery enables the retrieval of all records from the main query that are present in the result of the subquery.

Example

Retrieve all customers who have placed an order in the Orders table.

SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

NOT IN (SELECT)

The outcome in the example provided yielded 74 records, indicating that there are 17 customers who haven’t placed any orders.

Let’s confirm this by using the NOT IN operator.

Example

Retrieve all customers who have not placed any orders in the Orders table.

SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);