SQL Server: IN Condition

In this post explains how to use the IN condition in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in SQL Server (Transact-SQL) is:

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

OR

expression IN (subquery);

Parameters or Arguments

expression

A value to test.

value1, value2,.. value_n

The values to test against expression.

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.

Note

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

Example - With string

Let's look at a SQL Server IN condition example using string values.

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

SELECT *
FROM employees
WHERE last_name IN ('Smith', 'Mark', 'Johnson');

This SQL Server IN condition example would return all rows from the employees table where the last_name is either 'Smith', 'Mark', or 'Johnson'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE last_name = 'Smith'
OR last_name = 'Mark'
OR last_name = 'Johnson';

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

Example - With Numeric

Next, let's look at a SQL Server IN condition example using numeric values.

For example:

SELECT *
FROM employees
WHERE employee_id IN (1, 2, 3, 4, 10);

This SQL Server IN condition example would return all employees where the employee_id is either 1, 2, 3, 4, or 10.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE employee_id = 1
OR employee_id = 2
OR employee_id = 3
OR employee_id = 4
OR employee_id = 10;

Example - Using NOT operator

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

For example:

SELECT *
FROM employees
WHERE first_name NOT IN ('Julie', 'John', 'Dale');

This SQL Server IN condition example would return all rows from the employees table where the first_name is not 'Julie', 'John', or 'Dale' Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM employees
WHERE first_name > 'Julie'
AND first_name > 'John'
AND first_name > 'Dale';