SQL Server Transact SQL - Where In

Introduction To SQL Server Where In Clause

In this syntax, the column_name is the name of the column that you want to search, and the table_name is the name of the table where the column is located. The values enclosed in parentheses are the list of values you want to search for, separated by commas.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, value3, ...);

The condition can be any valid expression that evaluates to true or false. It can include The following operators comparison operators such as =, <, >, <=, >=, and <>, as well as logical operators such as AND, OR, and NOT.

For example, suppose you have a table called "Employees" that contains the columns "Name", "Department", and "Salary". You want to find all employees who work in the departments "Sales" or "Marketing". You can use the WHERE IN clause to filter the results as follows:

SQL Server Where Clause Using "In" Search Condition Examples

Here is an example of creating a employee table and "Department" in T-SQL and inserting data into it:

CREATE TABLE Department (
   DeptID INT PRIMARY KEY,
   DeptName VARCHAR(50)
);

CREATE TABLE Employees (
   EmpID INT PRIMARY KEY,
   EmpName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2),
   StartDate DATE
);

INSERT INTO Department VALUES 
   (1, 'Sales'),
   (2, 'Marketing'),
   (3, 'Finance'),
   (4, 'Human Resources');

INSERT INTO Employees VALUES 
   (1, 'John Smith', 1, 60000.00, '2020-01-01'),
   (2, 'Sarah Johnson', 1, 55000.00, '2018-07-15'),
   (3, 'Mark Davis', 2, 65000.00, '2019-03-01'),
   (4, 'Lisa Brown', 2, 50000.00, '2021-02-15'),
   (5, 'David Lee', 3, 75000.00, '2017-09-01'),
   (6, 'Emily Green', 4, 55000.00, '2019-05-01'),
   (7, 'Kevin White', 4, 45000.00, '2018-01-01');

In The following example, we first create a table "Department" with two columns "DeptID" and "DeptName". We set the "DeptID" as the primary key. Next, we create a table "Employees" with three columns "EmpID", "EmpName", and "DepartmentID". We set "EmpID" as the primary key.

We then insert some sample data into the "Department" table and "Employees" table.

Now, suppose we want to find all the employees who work in the "Sales" or "Marketing" department, we can the following select statement to leverage WHERE IN clause in T-SQL as follows:

SELECT EmpName
FROM Employees
WHERE DepartmentID IN (
   SELECT DeptID
   FROM Department
   WHERE DeptName IN ('Sales', 'Marketing')
);

This query will return the following records.

Multiple Conditions - Where In Clause

here's an example of the WHERE IN clause with multiple conditions from the "Employees" and "Department" tables we created earlier:

Suppose we want to find all the employees who work in the "Sales", "Marketing" or "Human Resources" department, and whose salary is greater than or equal to 50000. We can use the following query:

SELECT EmpName, Salary, DeptName
FROM Employees
JOIN Department ON Employees.DepartmentID = Department.DeptID
WHERE Department.DeptName IN ('Sales', 'Marketing', 'Human Resources')
AND Salary >= 50000;

In this query, we use the WHERE IN clause to specify the department names we want to search for. We also use the JOIN clause to join the "Employees" and "Department" tables based on the "DepartmentID" and "DeptID" columns. We then use the AND operator to specify the condition for the salary.

This query will return the name, salary, and department of all the employees who work in the "Sales", "Marketing" or "Human Resources" department, and the salary column is greater than or equal to 50000.

Finding Rows By Using A Comparison Operator

here's an example of using the WHERE IN clause with comparison operators in the "Employees" and "Department" tables we created earlier:

Suppose we want to find all the employees who work in the "Sales", "Marketing" or "Human Resources" department, whose salary is either greater than or equal to 50000 or less than or equal to 55000. We can use the following query:

SELECT EmpName, Salary, DeptName
FROM Employees
JOIN Department ON Employees.DepartmentID = Department.DeptID
WHERE Department.DeptName IN ('Sales', 'Marketing', 'Human Resources')
AND Salary >= 50000 AND Salary <= 55000;

The Result Set Is Below or rows returned

This query will return the name, salary, and department of all the employees who work in the "Sales", "Marketing" or "Human Resources" department, and whose salary is either greater than or equal to 50000 or less than or equal to 55000.

Additional Resources

SQL Server Transact SQL - Where In