SQL: IN Condition

This SQL tutorial explains how to use the SQL IN condition with syntax and examples.

Description

The SQL IN condition (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in SQL is:

expression IN (value1, value2, .... value_n);

OR

expression IN (subquery);

Parameters or Arguments

expression

This is a value to test.

value1, value2 ..., alue_n

These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true.

subquery

This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using the IN Condition with Character Values

The IN condition can be used with any data type in SQL. Let's look at how to use the IN condition with character (string) values.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods');

There will be 3 records selected. These are the results that you should see:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
300	Oracle	Redwood City	California
800	Flowers Foods	Thomasville	Georgia

This example would return all rows from the suppliers table where the supplier_name is either Microsoft, Oracle or Flowers Foods. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

It is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft'
OR supplier_name = 'Oracle'
OR supplier_name = 'Flowers Foods';

As you can see, using the IN condition makes the statement easier to read and more efficient than using multiple OR conditions.

Example - Using the IN Condition with Numeric Values

Next, let's look at how to use the IN condition with numeric values.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id IN (5000, 7000, 8000, 9000);

There will be 4 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
5000	Smith	Jane	digminecraft.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

This example would return all records from the customers table where the customer_id is either 5000, 7000, 8000 or 9000.

It is equivalent to the following SQL statement:

SELECT *
FROM customers
WHERE customer_id = 5000
OR customer_id = 7000
OR customer_id = 8000
OR customer_id = 9000;

Example - Using the IN Condition with the NOT Operator

Finally, let's look at how to use the IN condition with the NOT operator. The NOT operator is used to negate a condition. When we use the NOT operator with the IN condition, we create a NOT IN condition. This will test to see if an expression is not in a list.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following SQL statement:

SELECT *
FROM products
WHERE product_name NOT IN ('Pear', 'Banana', 'Bread');

There will be 4 records selected. These are the results that you should see:

product_id	product_name	category_id
3	Orange	50
4	Apple	50
6	Sliced Ham	25
7	Kleenex	NULL

This example would return all rows from the products table where the product_name is not Pear, Banana or Bread. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

It is equivalent to the following SQL statement:

SELECT *
FROM products
WHERE product_name <> 'Pear'
AND product_name <> 'Banana'
AND product_name <> 'Bread';

As you can see, the equivalent statement is written using AND conditions instead of OR conditions because the IN condition is negated.