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?

回答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.

回答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.

回答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)

回答4:

Here is an example of the answer that worked for me:

SELECT Count(1) 
    FROM LCSource as s
    JOIN FileTransaction as t
    ON s.TrackingNumber = t.TrackingNumber  
    WHERE NOT EXISTS (
        SELECT * FROM LCSourceFileTransaction 
        WHERE [LCSourceID] = s.[LCSourceID] AND [FileTransactionID] = t.[FileTransactionID]
    )

You see both columns exist in LCSourceFileTransaction, but one occurs in LCSource and one occurs in FileTransaction and LCSourceFileTransaction is a mapping table. I want to find all records where the combination of the two columns is not in the mapping table. This works great. Hope this helps someone.