SQL IN Operator - W3Schools

Home » What Does Mean In Sql » SQL IN Operator - W3Schools

Maybe your like

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.

Example

Return all customers from 'Germany', 'France', or 'UK'

SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); Try it Yourself »

Syntax

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

Demo Database

Below is a selection from the Customers table used 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 using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.

Example

Return all customers that are NOT from 'Germany', 'France', or 'UK':

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

IN (SELECT)

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

With a subquery you can return all records from the main query that are present in the result of the subquery.

Example

Return all customers that have an order in the Orders table:

SELECT * FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders); Try it Yourself »

NOT IN (SELECT)

The result in the example above returned 74 records, that means that there are 17 customers that haven't placed any orders.

Let us check if that is correct, by using the NOT IN operator.

Example

Return all customers that have NOT placed any orders in the Orders table:

SELECT * FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders); Try it Yourself » ❮ Previous Next ❯ ★ +1 Sign in to track progress

Tag » What Does Mean In Sql