T-SQL "Where not in" using two columns

Question

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?

Solution 1

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.

Solution 2

You can't do this using a WHERE IN type statement.

Instead you could LEFT JOIN to the target table (T2) and select where T2.ID is NULL.

For example

SELECT 
    T1.*
FROM
    T1 LEFT OUTER JOIN T2
    ON T1.A = T2.C AND T1.B = T2.D
WHERE
    T2.PrimaryKey IS NULL

will only return rows from T1 that don't have a corresponding row in T2.

Solution 3

I Used it in Mysql because in Mysql there isn't "EXCLUDE" statement.

This code:

1. Concates fields C and D of table T2 into one new field to make it easier to compare the columns.
2. Concates the fields A and B of table T1 into one new field to make it easier to compare the columns.
3. Selects all records where the value of the new field of T1 is not equal to the value of the new field of T2.

SQL-Statement:

SELECT T1.* FROM T1 
  WHERE CONCAT(T1.A,'Seperator', T1.B) NOT IN
    (SELECT CONCAT(T2.C,'Seperator', T2.D) FROM T2)

Comments

[Anders Lindén]

I am not sure I understand but it seems that Tomalak gave me an answer! Thanks however!

[RolandoCC]

This is an excelent option !

[Malus Jan]

Doesn't work. FirstTable isn't declare in second SELECT.

[FH-Inway]

Could you explain why this statement answers the question?

[FH-Inway]

Thanks. Could you take a look at your first sentence, I'm not sure what you want to say with that. The question was about T-SQL because MySQL does not support WHERE NOT IN.

[Malus Jan]

I had the same problem but in Mysql. When I solved it, I put my answer here maybe that would be useful for the people who work with mysql. This method also works for T-SQL.