TSQL: select values from a list that don’t exist in a table.
Use the VALUES keyword and the EXCEPT operator.
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!