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:


 

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.