Anders Lindén Anders Lindén - 1 year ago 90
SQL Question

T-SQL "Where not in" using two columns

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?

Answer Source

Use a correlated sub-query:

    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download