TSQL: select values from a list that don’t exist in a table.

Use the VALUES keyword and the EXCEPT operator.

Source code and explanation, by me.

Every now and then I need to find which value out of a list of known values does not exist in some database. Gap analysis, if you will.

And every time I need to look up how to do it.

Today that lead me to learn about the operators EXCEPT and INTERSECT in Microsoft’s SQL dialect, Transact-SQL.

A condition that limits their usability is their need to have the exact same amount of columns on both sides of the operator. That makes them ill-fitting substitutes for clauses like WHERE EXISTS or WHERE IN.

I compared a list of 160 values against a table containing 4,5 million records, on a column that has a non-unique index. The DBMS returned 64 missing values in 15ms: fast enough for me.

Happy coding!