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.