CODEWITHSUNDEEP
sqlsql-server-2008
carmbrester
carmbrester

Reputation: 930

Are there better ways to Select which IDs do not exist?

Is there a better way to achieve this same result?

I have a list of IDs (not in a table). I need to find which of the IDs does not exist in a given table. I've been able to achieve the results I'm looking for by executing the following SQL statement:

SELECT InlineID
FROM (SELECT 11111 AS InlineID UNION ALL SELECT 22222 UNION ALL SELECT 33333) AS Ids
LEFT OUTER JOIN MyTable ON Ids.InlineID = MyTable.ID
WHERE MyTable.ID IS NULL;

While this works, it feels as though there ought to be a simpler way to achieve the same result, i.e. the repetitive UNION ALL statements look messy, especially when the list of IDs grows large. One other restriction is that I don't have rights to be able to create temp tables, so those types of solutions will not be possible.

Upvotes: 2

Views: 106

Answers (1)

Martin Smith
Martin Smith

Reputation: 453453

SELECT InlineID
FROM   (VALUES (11111),
               (22222),
               (33333)) T(InlineID)
EXCEPT
SELECT ID
FROM   MyTable

Upvotes: 7

Related Questions