IN Condition

Course- SQL >

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

Description

The SQL IN condition is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the SQL IN condition is:

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

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. This is a quick method to test if any one of the values matches expression.

Note

The SQL IN condition will return the records where expression is value1, value2..., or value_n.
The SQL IN condition is also called the SQL IN operator.

Example - With Character

Let's look at an IN condition example using character values.

The following is a SQL SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM suppliers
WHERE supplier_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the SQL IN condition makes the statement easier to read and more efficient.

Example - With Numeric

Next, let's look at an IN condition example using numeric values.

For example:

SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);

This SQL IN condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example - Using NOT operator

Finally, let's look at an IN condition example using the NOT operator.