I want to select all records from a table T1 where the values in columns A and B has no matching tuple for the columns C and D in table T2.
In mysql “Where not in” using two columns I can read how to accomplish that using the form select A,B from T1 where (A,B) not in (SELECT C,D from T2), but that fails in T-SQL for me resulting in "Incorrect syntax near ','.".
So how do I do this?
Use a correlated sub-query:
... WHERE NOT EXISTS ( SELECT * FROM SecondaryTable WHERE c = FirstTable.a AND d = FirstTable.b )
Make sure there's a composite index on SecondaryTable over
(c, d), unless that table does not contain many rows.