SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL IN Operator

❮ Previous Next ❯

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Syntax

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

You can also use NOT IN to find records that do not match any of the values in the list.

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

Demo Database

Below is a selection from the Customers table:

Customers Table:

CustomerID CustomerName Country
1 Alfreds Futterkiste Germany
2 Ana Trujillo Emparedados Mexico
3 Around the Horn UK
4 Berglunds snabbköp Sweden

Example 1: Using IN

The following SQL statement selects all customers that are located in "Germany", "France", or "UK".

IN Example

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

This is a more concise and readable way of writing:

SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'France' OR Country = 'UK';

Example 2: Using NOT IN

The following SQL statement selects all customers that are NOT located in "Germany", "France", or "UK".

NOT IN Example

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

Example 3: IN with a Subquery

The IN operator can also be used with a subquery to select records from one table based on values in another.

The following SQL statement selects all customers that have placed an order:

SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);
❮ Previous Next ❯