SQL Server IN Operator

 

This SQL tutorial focuses on the SQL Server IN Operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in SQL Server. To read additional posts regarding this subject, please use the following links:

• Introduction – SQL Server WHERE clause
• Comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

---

 

SQL Server IN Operator

In SQL Server, The IN Operator is used to test whether a value is “in” a specified list.

WHERE  column_name IN (value, value, value ..)

 

SQL Server IN Operator with Numeric Values

This SQL Server IN example would return all employees whose department number equals 50, 80, or 90:

SELECT *
FROM employees
WHERE  department_id IN (50, 80, 90)

You can achieve the same result by using the OR operator:

SELECT *
FROM employees
WHERE  department_id = 50 
OR     department_id = 80
OR     department_id = 90

 

SQL Server IN Operator with Character Values

This SQL Server example would return all employees whose last name is equal to Smith, Jones, or Stevenson:

SELECT last_name, first_name, salary
FROM employees
WHERE last_name IN ('Smith', 'Jones', 'Stevenson')

• The strings values must be enclosed within single quotes.
• In SQL Server, string values are not case-sensitive by default.
• You can achieve the same result by using the OR operator:

SELECT last_name, first_name, salary
FROM employees
WHERE last_name = 'Smith'
OR    last_name = 'Jones'
OR    last_name = 'Stevenson'

 

SQL Server IN Operator with Date Values

This SQL Server example would return all employees whose date of employment is equal to 01.JAN.1990 or 07.SEP.1982:

SELECT employee_id, hire_date, salary
FROM employees
 WHERE hire_date IN ('1982-09-07', '1990-01-01')

• Date values must be enclosed within single quotes (‘date value’).
• Date values are format sensitive.
• You can achieve the same result by using the OR operator:

SELECT employee_id, hire_date, salary
FROM employees
WHERE hire_date = '1982-09-07'
OR    hire_date = '1990-01-01'

 

SQL Server NOT Operator

• The SQL Server NOT operator is used to produce opposite results than those produced by the operator IN.
• The NOT keyword should be written before the IN keywrod.

This SQL Server example would return all employees whose department number equals 50, 80, or 90:

SELECT *
FROM employees
WHERE  department_id IN (50, 80 ,90)

This SQL Server example would return all employees whose department number does not equal 50, 80, or 90:

SELECT *
FROM employees
WHERE  department_id NOT IN (50, 80 ,90)

The column data type must match the data type of the values within the round brackets.