| The IN Set Operator
with Multiple Columns
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 |
|