SELECT those not found in IN() list

Apologies if there is an answer to this already, I searched and probably couldnt think up the right keywords to find it.

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?

回答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

回答2:

There is nothing wrong with LEFT JOIN but you may also use not exists

SELECT * 
FROM Customers c
WHERE not exists (select * from CustomerDetails ds where ds.CustomerId = c.CustomerId)