fbpx

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:


 

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.

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…