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

October 8, 2005

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 expession. We have to do this on both side of the IN operator.

This is the code sample:

USE AdventureWorks;
GO
SELECT FirstName, LastName
FROM Person.Contact AS c
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 partial result set:

FirstName	LastName
Garret	Vargas
David	Cambell