The T-SQL IN operator can be used to test whether a given value matches any item in a particular list or sub-query. If the given value equals an item in the list or sub-query the expression returns true, otherwise it returns false.
The following example (based on AdventureWorks) uses IN to return all the departments which are in the research and development, or quality assurance groups. Below is the full list of departments:
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
1 Engineering Research and Development 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
3 Sales Sales and Marketing 1998-06-01 00:00:00.000
4 Marketing Sales and Marketing 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
7 Production Manufacturing 1998-06-01 00:00:00.000
8 Production Control Manufacturing 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
10 Finance Executive General and Administration 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
16 Executive Executive General and Administration 1998-06-01 00:00:00.000
(16 row(s) affected)
Now to get only the departments that are in the groups research and development or quality assurance. We need to use the IN operator in the WHERE clause to restrict the rows returned:
1 2 3 4 5 6 7 8 9 | SELECT * FROM HumanResources.Department WHERE GroupName IN ( 'Research and Development' , 'Quality Assurance' ) |
This gives us the following result:
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
1 Engineering Research and Development 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
6 Research and Development Research and Development 1998-06-01 00:00:00.000
12 Document Control Quality Assurance 1998-06-01 00:00:00.000
13 Quality Assurance Quality Assurance 1998-06-01 00:00:00.000
(5 row(s) affected)
We can also include NOT with the IN operator, which logically returns false when the value matches an item in the list and true otherwise. So we could get all departments that aren’t in the above groups:
1 2 3 4 5 6 7 8 9 | SELECT * FROM HumanResources.Department WHERE GroupName NOT IN ( 'Research and Development' , 'Quality Assurance' ) |
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
3 Sales Sales and Marketing 1998-06-01 00:00:00.000
4 Marketing Sales and Marketing 1998-06-01 00:00:00.000
5 Purchasing Inventory Management 1998-06-01 00:00:00.000
7 Production Manufacturing 1998-06-01 00:00:00.000
8 Production Control Manufacturing 1998-06-01 00:00:00.000
9 Human Resources Executive General and Administration 1998-06-01 00:00:00.000
10 Finance Executive General and Administration 1998-06-01 00:00:00.000
11 Information Services Executive General and Administration 1998-06-01 00:00:00.000
14 Facilities and Maintenance Executive General and Administration 1998-06-01 00:00:00.000
15 Shipping and Receiving Inventory Management 1998-06-01 00:00:00.000
16 Executive Executive General and Administration 1998-06-01 00:00:00.000
(11 row(s) affected)
We can also use sub-queries with the IN operator. For example we might want to get a list of employee id’s that have been assigned to a department within the Quality Assurance group:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT EmployeeID FROM HumanResources.EmployeeDepartmentHistory WHERE DepartmentID IN ( SELECT DepartmentID FROM HumanResources.Department WHERE GroupName = 'Quality Assurance' ) |
