使用两列的T-SQL“Where in in”

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.

我想从表T1中选择所有记录，其中列A和B中的值对于表T2中的列C和D没有匹配的元组。

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 ','.".

在mysql“Where not in”中使用两列我可以阅读如何使用表格选择A，B来自T1，其中（A，B）不在（SELECT C，D从T2），但在T-SQL中失败对我来说导致“语法不正确'，'。”。

So how do I do this?

那我该怎么做？

3 个解决方案

#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.

确保SecondaryTable上的复合索引超过（c，d），除非该表不包含很多行。

#2

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

您不能使用WHERE IN类型语句执行此操作。

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

相反，您可以LEFT JOIN到目标表（T2）并选择T2.ID为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.

只返回T1中没有T2对应行的行。

#3

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

我在Mysql中使用它，因为在Mysql中没有“EXCLUDE”语句。

This code:

这段代码：

Concates fields C and D of table T2 into one new field to make it easier to compare the columns.
将表T2的字段C和D合并为一个新字段，以便更容易地比较列。
Concates the fields A and B of table T1 into one new field to make it easier to compare the columns.
将表T1的字段A和B汇总到一个新字段中，以便更容易地比较列。
Selects all records where the value of the new field of T1 is not equal to the value of the new field of T2.
选择T1的新字段的值不等于T2的新字段的值的所有记录。

SQL-Statement:

的SQL语句：

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

#1