SELECT those not found in IN() list

Question

I have a table with over 1000 tables (e.g Customers).

I have a query requiring details of a known list of customers (e.g by CustomerID - 1,79,14,100,123)

The IN() function is what I would like to use for the query.

I know to find customers that match the list, I would write:

SELECT * FROM Customers WHERE CustomerID IN (1,79,14,100,123) 

To find those that are not in the list, I would write

SELECT * FROM Customers WHERE CustomerID NOT IN (1,79,14,100,123) 

Question

How do I find the list of Customers that where NOT returned or did not find a match from the list?

Suppose the Customers table only has (1,79,100). Then it would mean 14 and 123 will not be matched. How do I find those values that do not find a match.

I was simplifying in my example. My list of items has over 300 IDs, so using WHERE condition with a long list of OR would be cumbersome/clumsy. I have thought of combining with self LEFT JOIN and identifying the NULL paired values, which would be 14 and 123

Is there a more elegant approach?

Answer 1

You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT.

The below uses a table value constructor as a derived table (compatible with SQL Server 2008+)

SELECT CustomerId FROM   (VALUES(1),               (79),               (14),               (100),               (123)) V(CustomerId) EXCEPT SELECT CustomerId FROM   Customers