Introduction
SQL Server IN operator checks whether a value matches any value in a specified list or returned by a subquery. It provides a concise alternative to writing several equality comparisons joined with OR. You can use IN in a WHERE clause to filter rows, in a HAVING clause to filter groups, and with subqueries to compare data across related tables.
SQL Server IN Operator Syntax
The basic syntax uses an expression followed by IN and a parenthesized list of values:
SELECT column_list
FROM table_name
WHERE expression IN (value1, value2, value3);
SQL Server evaluates the expression and checks whether it equals at least one item in the list. If a match exists, the condition is TRUE and the row qualifies. If no match exists, the condition is generally FALSE, subject to SQL’s NULL logic discussed below.
Example Table
Consider an Employees table with columns such as EmployeeID, EmployeeName, Department, City, and Salary. The following examples use this table to demonstrate practical filtering patterns.
Using IN with a List of Values
To retrieve employees who work in the IT, HR, or Finance departments, specify those department names in an IN list:
SELECT EmployeeID, EmployeeName, Department
FROM Employees
WHERE Department IN ('IT', 'HR', 'Finance');
The query returns rows whose Department is equal to any one of the three supplied values. It is equivalent to the following condition:
WHERE Department = 'IT'
OR Department = 'HR'
OR Department = 'Finance'
Using IN is usually easier to read when several alternatives are involved. It also makes the list of accepted values easier to maintain.
Using IN with Numeric Values
The operator works with numbers as well as text. For example, to retrieve specific employee records by their IDs:
SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE EmployeeID IN (101, 105, 112, 120);
SQL Server returns employees whose IDs match one of the listed integers. Numeric values are written without quotation marks. Text values, by contrast, are normally enclosed in single quotation marks.
Using IN with the NOT Operator
NOT IN selects rows whose expression does not match any value in the supplied list. For example, to exclude employees from the HR and Finance departments:
SELECT EmployeeID, EmployeeName, Department
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');
This returns rows from departments other than HR and Finance. Be aware that NULL values require special attention: a NULL Department does not satisfy this predicate and will not be returned. More importantly, a NULL inside a NOT IN list can cause the predicate to evaluate to UNKNOWN for otherwise nonmatching values. When the list comes from a table or subquery that may contain NULL, consider NOT EXISTS instead.
Using IN with a Subquery
An IN operator can use a subquery instead of a manually typed list. This is useful when the matching values are stored in another table and may change over time. Suppose a Departments table contains a DepartmentName and an IsActive column. To return employees belonging to active departments:
SELECT EmployeeID, EmployeeName, Department
FROM Employees
WHERE Department IN (
SELECT DepartmentName
FROM Departments
WHERE IsActive = 1
);
The inner query returns the names of active departments. The outer query returns employees whose Department matches one of those names. SQL Server can optimize this pattern as a semi-join; you do not need to manually run the subquery first.
IN with a WHERE Clause and Multiple Conditions
You can combine an IN predicate with other conditions using AND and OR. Parentheses make the intended logic clear and prevent mistakes caused by operator precedence.
SELECT EmployeeID, EmployeeName, Department, Salary
FROM Employees
WHERE Department IN ('IT', 'Finance')
AND Salary >= 50000;
This query returns employees in either IT or Finance whose salary is at least 50,000. To add an alternative city condition, group the department test explicitly:
SELECT EmployeeID, EmployeeName, Department, City
FROM Employees
WHERE (Department IN ('IT', 'Finance'))
AND (City = 'Dhaka' OR City = 'Chattogram');
Using IN in a HAVING Clause
The HAVING clause filters grouped results after aggregation. You can use IN to retain only selected group values:
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING Department IN ('IT', 'HR', 'Finance');
This query groups employees by department, counts the rows in each group, and displays only the selected departments. You can also combine the list with an aggregate condition:
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING Department IN ('IT', 'Finance')
AND AVG(Salary) > 60000;
Using IN with UPDATE
The IN operator can limit which rows an UPDATE statement changes. Always check the target rows with a SELECT before running a data-changing statement, particularly in production databases.
UPDATE Employees
SET Salary = Salary * 1.05
WHERE EmployeeID IN (101, 105, 112);
Only the employees whose IDs appear in the list receive the five-percent increase. You can preview the affected records with the same WHERE condition in a SELECT statement.
Using IN with DELETE
You can also delete a selected set of records using IN. The following example removes employees with the specified IDs:
DELETE FROM Employees
WHERE EmployeeID IN (101, 105, 112);
Deletion is permanent unless your application or database recovery strategy provides a way to restore the rows. For important data, use a transaction and verify the target rows before committing.
BEGIN TRANSACTION;
DELETE FROM Employees
WHERE EmployeeID IN (101, 105, 112);
-- Verify the result, then choose one:
-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;
IN and NULL Values
SQL Server uses three-valued logic: a condition can be TRUE, FALSE, or UNKNOWN. NULL represents an unknown or missing value, so it cannot be matched with ordinary equality. For example, Department IN ('IT', NULL) does not mean “IT or missing department.” For a row with Department set to NULL, the comparison is UNKNOWN.
To include NULL values explicitly, combine IN with IS NULL:
SELECT EmployeeID, EmployeeName, Department
FROM Employees
WHERE Department IN ('IT', 'Finance')
OR Department IS NULL;
Use IS NULL rather than = NULL. The latter does not test for missing values in SQL Server.
NOT IN and NOT EXISTS
When excluding values returned by another table, NULL handling is especially important. Suppose a subquery returns a NULL value. A predicate such as EmployeeID NOT IN (SELECT EmployeeID FROM ExcludedEmployees) can evaluate to UNKNOWN for rows that do not match any non-NULL ID, which means those rows are filtered out.
A correlated NOT EXISTS is often safer for this anti-match requirement:
SELECT e.EmployeeID, e.EmployeeName
FROM Employees AS e
WHERE NOT EXISTS (
SELECT 1
FROM ExcludedEmployees AS x
WHERE x.EmployeeID = e.EmployeeID
);
NOT EXISTS checks whether the subquery finds a matching row. A NULL elsewhere in the subquery result does not poison the predicate. Choose the form that expresses the intended relationship and verify behavior with nullable data.
IN vs OR
For equality checks against several alternatives, IN is a compact way to express the same logic as a chain of OR conditions:
-- IN form
SELECT *
FROM Employees
WHERE Department IN ('IT', 'HR', 'Finance');
-- Equivalent equality alternatives
SELECT *
FROM Employees
WHERE Department = 'IT'
OR Department = 'HR'
OR Department = 'Finance';
The two forms are logically equivalent for these non-NULL list values. IN is generally clearer as the list grows. Use OR when the alternatives are different kinds of predicates rather than equality checks against one expression.
Using IN with a Table-Valued Parameter
Applications sometimes need to pass a variable collection of IDs to a stored procedure. A table-valued parameter can represent that collection as a table and be queried with IN or EXISTS. This approach is easier to maintain than concatenating user input into a SQL string and helps avoid SQL injection risks associated with dynamic SQL.
-- Example query when @EmployeeIds is a table-valued parameter
SELECT e.EmployeeID, e.EmployeeName
FROM Employees AS e
WHERE e.EmployeeID IN (
SELECT EmployeeID
FROM @EmployeeIds
);
Define the table type and pass the parameter using your application’s database driver. Keep the query parameterized; do not build an IN list by directly concatenating untrusted input.