The IN operator is used in a WHERE clause to check if a value matches any value in a list or subquery.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);
  • The column value is compared against all values in the parentheses.
  • Equivalent to using multiple OR conditions.

Step 1: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 2: Using IN with a list of values

Example: Employees in IT or HR department

SELECT FirstName, Department
FROM Employees
WHERE Department IN ('IT', 'HR');

Result:

FirstName Department
John IT
Jane HR
Mike IT
Sara HR
Tom IT

Equivalent to:

WHERE Department='IT' OR Department='HR'

Step 3: Using IN with numbers

Example: Employees with specific EmployeeIDs

SELECT FirstName, EmployeeID
FROM Employees
WHERE EmployeeID IN (1, 3, 5);

Result:

FirstName EmployeeID
John 1
Mike 3
Tom 5

Step 4: Using NOT IN

NOT IN returns rows not matching any value in the list.

SELECT FirstName, Department
FROM Employees
WHERE Department NOT IN ('IT', 'HR');

Result:

FirstName Department
(No rows in this example)

Use NOT IN to exclude specific values.

Step 5: Using IN with a subquery

IN can also compare a column against results from another query.

Example: Employees whose department exists in another table Departments

SELECT FirstName, Department
FROM Employees
WHERE Department IN (SELECT DepartmentName FROM Departments);

This allows dynamic filtering based on another table.

Categorized in:

SQL Server,