TSQL Multiple Column Where In Sub-Query Filter

Sometimes we want to filter query results based on results from multiple fields from a sub-query or in a where clause but if you try this you’ll get and error message:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Let’s say that we are trying to join results of two queries in a union table but we want to omit duplicates. In this case we don’t really want to use exists or not exists because our goal is to eliminate specific results from a result set… a sort of combination of exists and not exists.

My solution is to concatenate the desired columns into one column. That way all your criteria is evaluated in a single expression.

For instance:
 ... WHERE t.name + c.name + cast(c.column_id as nvarchar(3)) NOT IN (
 select t.name + c.name + cast(c.column_id as nvarchar(3))
 FROM ... )

Here is a practical example of a situation where I used this concatenation tip to eliminate duplicate results in a union table of keys and constraints. It’s useful as a stored procedure because it allows you to pass a list of tables copy/pasted from Excel (or from a query grid result set) without having to add a delimiter. The line break that is natural to copying multiple records from Excel is the delimiter.

/*
 Name: sp_FindContraints
 Description: Get Primary keys and required fields
 Author: Tim Bartel
 Modification Log: Change
 Description Date Changed By
 Created procedure 07/28/2014 Tim Bartel

Intructions: Pass list of table names to this stored procedure.
 Requirements: List MUST contain line breaks.
 Inclusions: List may contain duplicates, list may contain blank or empty rows

Example:
 exec sp_FindContraints 'MyTable_001
 MyTable_002
 MyTable_001

MyTable_003
 MyTable_004
 MyTable_006
 MyTable_009
 MyTable_007
 MyTable_009

MyTable_010
 ';
*/

If object_id('dbo.sp_FindContraints') IS NOT NULL
 DROP PROC dbo.sp_FindContraints
 GO

CREATE PROCEDURE sp_FindContraints
@tablelist varchar(max) -- list of table names. May contain line breaks.
AS
 BEGIN
 SELECT [Table Name], [Column Name], [Constraint Type] FROM
 (-- Find keys by table name
 select t.name AS 'Table Name', c.name AS 'Column Name', k.type_desc AS 'Constraint Type'
 FROM sys.indexes i
 INNER JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
 INNER JOIN sys.columns c ON c.object_id=i.object_id AND c.column_id=ic.column_id
 INNER JOIN sys.tables t ON t.object_id = c.object_id
 INNER JOIN sys.key_constraints k ON k.parent_object_id=i.object_id AND k.name=i.name
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 UNION ALL
 -- Find non nullable fields
 select t.name AS 'Table Name', c.name AS 'Column', CASE is_nullable
 WHEN 0 THEN 'Not NULL' ELSE ''END AS 'Constraint'
 from sys.tables t
 INNER JOIN sys.columns c ON t.object_id = c.object_id
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 AND is_nullable=0
 AND t.name + c.name + cast(c.column_id as nvarchar(3)) NOT IN ( -- Remove fields with keys
 select t.name + c.name + cast(c.column_id as nvarchar(3))
 FROM sys.indexes i
 INNER JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
 INNER JOIN sys.columns c ON c.object_id=i.object_id AND c.column_id=ic.column_id
 INNER JOIN sys.tables t ON t.object_id = c.object_id
 INNER JOIN sys.key_constraints k ON k.parent_object_id=i.object_id AND k.name=i.name
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 )
 ) Keys
 ORDER BY [Table Name], case [Constraint Type] WHEN 'Not NULL' THEN 99 ELSE 1 END
END