There is a table variable and a table.

How to leave in a variable only those rows that are missing in the table (using linking tables by a set of fields)?

I use for this merge :

 merge @re tar using (select Hash, TypeID from Hashes) src on tar.Hash = src.Hash and tar.TypeID = src.TypeID when matched then delete ; select * from @re

Not sure if this is the best option.

Purpose: use the interface for the application to check a small list on a large table in the database for presence in the database and continue processing in the program for missing values. (I don’t want to unload the entire table)

  • one
    In mssql xor this is ^ . In Boolean algebra, Cho, this inequality <> for all sql. - nick_n_a
  • one
    In any case, specify the DBMS. mssql mysql oracle. For different in different ways. Very similar to mssql - nick_n_a
  • one
    And if select * from @re tar left join Hashes src on tar.Hash = src.Hash and tar.TypeID = src.TypeID where src.Hash is null ? In general, execution plans should be compared - Mike
  • Here is one of the similar answers to so ru.stackoverflow.com/questions/474976/... well, if you want to speed up, type md5 across all fields, and compare md5 first. - nick_n_a
  • one
    Take a look here - i-one

1 answer 1

If you reformulate your question - then you need to remove from the variable those records that are in the table. Pretty simple, isn't it?

 delete from tar from @re tar inner join Hashes on tar.Hash = Hashes.Hash and tar.TypeID = Hashes.TypeID

If, on the contrary, only the records missing in the table need to be deleted, then an external connection and null check are used.

 delete from tar from @re tar left join Hashes on tar.Hash = Hashes.Hash and tar.TypeID = Hashes.TypeID where Hashes.TypeID is null

The use of merge is justified in cases when you need to perform different actions with one set (the records present are updated, the missing ones are added, or something like that).