DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
 

 

The IN Set Operator with Multiple Columns

By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, 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 quick syntax

SELECT * FROM AdventureWorks2008.Production.Product

WHERE Color IN ('Yellow','Black') ORDER BY ProductNumber

------------

-- SQL IN operator - SQL multiple columns IN operation

-/***** WARNING - avoid rounding & similar operations in the conversion *****/

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(10),convert(SMALLMONEY,0.01),2)

       IN (SELECT convert(VARCHAR,SalesPersonID) + '|' +

                  convert(VARCHAR(10),CommissionPct,2)

           FROM   Sales.SalesPerson

           WHERE  SalesQuota > 100000);

GO


This is the result set:
FirstName LastName
Garrett Vargas
Tsvi Reiter
Pamela Ansman-Wolfe
Shu Ito

 

Using equivalent OUTER JOIN instead of the IN operator:

 

USE AdventureWorks;

GO

 

SELECT FirstName,

       LastName

FROM   Person.Contact AS c

       INNER JOIN HumanResources.Employee AS e

         ON e.ContactID = c.ContactID

       RIGHT JOIN Sales.SalesPerson sp

         ON e.EmployeeID = sp.SalesPersonID

            AND 0.01 = CommissionPct

WHERE  SalesQuota > 100000

       AND e.EmployeeID IS NOT NULL;

GO

/*    FirstName   LastName

      Garrett     Vargas

      Tsvi        Reiter

      Pamela      Ansman-Wolfe

      Shu         Ito

*/

 

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

------------

Related article:

SQL WHERE.. IN clause multiple columns

Outer Join on Multiple Fields

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE