A blog about designing and developing Business Intelligence solutions using Microsoft SQL Server
Header

Transact-SQL IN

August 20th, 2012 | Posted by David Stewart in T-SQL - (0 Comments)

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:

DepartmentID Name                                               GroupName                                          ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
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:

DepartmentID Name                                               GroupName                                          ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
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'
   )
DepartmentID Name                                               GroupName                                          ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
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'
   )