SQLUSA
BI TRIO 2008
FREE TRIAL
CLICK HERE TO ORDER

SQL Server 2008 Best Practices
SQL Server 2005 Best Practices

 

Microsoft SQL Server 2005 Articles

 

The IN Set Operator with Multiple Columns

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

 

The World Leader in SQL Server 2008 Training
The future is just a CLICK away. Your future!
 
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.