SQL Server List Operators

SQL Server - List Operators

List operators are used to retrieve the data using the condition specified with list of values.

SQL Server supports list operators such as IN and NOT IN.

• IN: specifies condition in inclusive list of values to search the rows in database table.
• NOT IN: specifies condition in exclusive list of values to search the rows in database table.

List operators can be used in the WHERE clause.

List Operators Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE expression1 [IN| NOT NOT] (value-1, value-2,...value-n) 

Employee Table

Let us consider the example 'Employee' database table.

IN Operator on WHERE Statement

IN operator is used to specify the condition based on list of values in WHERE clasue.

IN Operator Example

To get rows based on salary either 6000 or 8000 in a Employee table.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE Salary IN (6000, 8000)

To get rows based on DeptId either 1 or 2.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE DeptId IN (1, 3)

IN Operator on WHERE Statement

NOT IN operator is used to specify the condition based on list of values in WHERE clasue.

NOT IN Operator Example

To get rows based on salary except 6000 and 8000 in a Employee table.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE Salary NOT IN (6000, 8000)

To get rows based on DeptId except 1 and 2.

SELECT Name, DeptId, City, Email, Salary
FROM Employee
WHERE DeptId NOT IN (1, 3)