question

It sounds like what you want to get is more like the output from an outer join. You will not be able to get the result you want by using an IN clause declare @tblcustomers table (customer_id int, customer_name varchar(50)) insert into @tblcustomers select 1, 'Ali1' insert into @tblcustomers select 2, 'Ali2' insert into @tblcustomers select 3, 'Ali3' select TableInsteadOfINClause.CustomerID, tblcustomers.customer_name from @tblcustomers tblcustomers right join (select 1 as CustomerID union select 4 union select 5) as TableInsteadOfINClause on TableInsteadOfINClause.CustomerID = tblcustomers.customer_id gives CustomerID customer_name ----------- ------------------ 1 Ali1 4 NULL 5 NULL

This produces the output you seem to be looking for: ![Sample output][1] DECLARE @Customers AS TABLE ( customer_id integer PRIMARY KEY, customer_name varchar(50) ); INSERT @Customers (customer_id, customer_name) VALUES (1, 'Ali1'), (2, 'Ali2'), (3, 'Ali3'); DECLARE @CustomerCSV varchar(8000) = '1,4,5,6,7,8,9,10,11,12,13,14,15'; SELECT CustomerID = dsk.ItemNumber FROM dbo.DelimitedSplit8K(@CustomerCSV, ',') AS dsk WHERE NOT EXISTS ( SELECT * FROM @Customers AS c WHERE c.customer_id = dsk.ItemNumber ); That sample uses a generic string-splitting function: -- General utility string-splitting function CREATE FUNCTION dbo.DelimitedSplit8K ( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), cteTally(N) AS ( SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E4 ), cteStart(N1) AS ( SELECT t.N+1 FROM cteTally AS t WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) ) SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1), Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)) FROM cteStart AS s; GO Function adapted slightly from an [SSC article][2] by @Jeff Moden [1]: http://ask.sqlservercentral.com/storage/temp/16-Ask.jpg [2]: http://www.sqlservercentral.com/articles/Tally+Table/72993/