By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

July 28, 2009

The IN set operator is a favorite with SQL developers due to its natural language type constructs. The test expression can be a colum or literal. The searched set can be a set of literals or a subquery that has a result set of one column. This column must have the same data type as the test expression.

Frequently the test expression is several columns or a combination of literals and columns. In such case the IN keyword would not work. The solution is to combine all the columns into one column. The simplest and most convenient combination is converting all items to varchar and concatinate them into one string expression. We have to do this on both side of the IN operator.

This is the first T-SQL code sample:

-- SQL IN operator

-- SQL multiple columns IN operation

USE AdventureWorks;

GO

SELECT FirstName,

       LastName

FROM   Person.Contact AS c

       INNER JOIN HumanResources.Employee AS e

         ON e.ContactID = c.ContactID

WHERE  convert(VARCHAR,EmployeeID) + convert(VARCHAR,0.01)

       IN (SELECT convert(VARCHAR,SalesPersonID) +

                  convert(VARCHAR,CommissionPct)

           FROM   Sales.SalesPerson

           WHERE  SalesQuota > 100000);

GO

This is the result set:

FirstName	LastName
Michael	Blythe
Garrett	Vargas
Tsvi	Reiter
Pamela	Ansman-Wolfe
Shu	Ito
David	Campbell

Following is the second Microsoft SQL Server T-SQL code sample:

-- SQL multiple column IN operator

-- Using equivalent inner join expression

USE tempdb;

-- SELECT INTO create table for demo

SELECT TOP ( 10 ) ProductID,

                  Color,

                  ListPrice

INTO   Alpha

FROM   AdventureWorks2008.Production.Product

WHERE  Color IS NOT NULL

       AND ListPrice > 0

GO

SELECT * FROM   Alpha

GO

/*    ProductID   Color       ListPrice

680         Black       1431.50

706         Red         1431.50

707         Red         34.99

708         Black       34.99

709         White       9.50

710         White       9.50

711         Blue        34.99

712         Multi       8.99

713         Multi       49.99

714         Multi       49.99

*/

-- SQL IN operator with multiple columns

SELECT *

FROM   AdventureWorks2008.Production.Product

WHERE  '|' + Color + '|' + convert(VARCHAR,ListPrice) + '|'

  IN (SELECT '|' + Color + '|' + convert(VARCHAR,ListPrice) + '|'

      FROM   Alpha)

-- (21 row(s) affected)

-- Equivalent inner join query

SELECT DISTINCT p.*

FROM   AdventureWorks2008.Production.Product p

       INNER JOIN Alpha a

         ON p.Color = a.Color

            AND p.ListPrice = a.ListPrice

GO

-- (21 row(s) affected)

-- Cleanup

DROP TABLE tempdb.dbo.Alpha

GO