使用SQL查找不匹配的记录

Question

我正在尝试编写一个查询，查找在另一个表中没有匹配记录的记录。

例如，我有两个表格，它们的结构看起来像这样：

Table1
    State | Product | Distributor | other fields
    CA    | P1      |  A          | xxxx
    OR    | P1      |  A          | xxxx
    OR    | P1      |  B          | xxxx
    OR    | P1      |  X          | xxxx
    WA    | P1      |  X          | xxxx
    VA    | P2      |  A          | xxxx
Table2
    State | Product | Version | other fields
    CA    | P1      |  1.0    | xxxx
    OR    | P1      |  1.5    | xxxx
    WA    | P1      |  1.0    | xxxx
    VA    | P2      |  1.2    | xxxx

（State/Product/Distributor一起形成Table1的关键字。State/Product是Table2的关键字）

我想查找所有不使用经销商X的State/Product/Version组合。 (因此，在本例中的结果是CA-P1-1.0和VA-P2-1.2.)

您有什么查询建议吗？

- Boofus McGoofus

---

从倒数第二句开始翻译，table2 在这个查询中是否应该参与？（除了获取产品版本之外）。 - Tundey

我认为你已经回答了自己的问题。Table2是获取版本所必需的。 - J.T. Grimes

Answer 1

SELECT
    *
FROM
    Table2 T2
WHERE
    NOT EXISTS (SELECT *
        FROM
           Table1 T1
        WHERE
           T1.State = T2.State AND
           T1.Product = T2.Product AND
           T1.Distributor = 'X')

这应该符合 ANSI 标准。

- gbn

---

我和我的SQL Server MVP同事经常讨论这个问题 :-) 在编译时展开，但是他说它在运行时不会轻易折叠。他曾经向我展示过一篇文章。我有一段时间没见Itzak Ben-Gan了，但他说更快。选择权在你手中... - gbn

@Dems 这并不完全正确。在Oracle中，SELECT * 在EXISTS子句中被特别优化。后来，在互联网群体开始推荐使用SELECT 1之后，他们不得不回过头来改变他们的代码以适应它。此外，根据至少一篇博客在大约一年前的报道，SELECT 1在Sql Server中在执行计划构建期间更有效率。这是一个抉择... 显然，进行全球性的推荐是充满风险的。 - ErikE

@Emtucifor：你有SQL Server中那个SELECT 1的参考资料吗？我之前听过这个，但从未见过任何权威的资料。 - gbn

请参阅微软员工Conor Cunningham的旧博客（抱歉，帖子本身有一个URL问题，请扫描页面），还请看看Lutz Mueller在这篇博客文章的评论中使用IF EXISTS()时出现了性能差异。然而，我并不是在声称这就是Sql Server实际操作的方式，只是我_读到_了这样的说法。 - ErikE

@Emtucifor：太好了。谢谢你。这支持了我之前发布的内容 http://stackoverflow.com/questions/1932386/how-to-avoid-null-when-using-value-name-mapping-in-sql/1932406#1932406 - gbn

Answer 2

T-SQL语言中：

SELECT DISTINCT Table2.State, Table2.Product, Table2.Version
FROM Table2 
  LEFT JOIN Table1 ON Table1.State = Table2.State AND Table1.Product = Table2.Product AND Table1.Distributor = 'X'
WHERE Table1.Distributor IS NULL

不需要子查询。

编辑：正如评论所指出的，DISTINCT不是必需的。谢谢！

- mwigdahl

---

我不会使用distinct，但除此之外，这就是你想要的。 - HLGEM

使用DISTINCT可能会使查询变得不太高效，但永远不会更高效。这取决于相对表行计数。DISTCNT还会强制执行子查询不需要的聚合。 - gbn

此查询的性能和行为也在很大程度上取决于连接中使用的索引是否覆盖了“Distributor”（如果没有，则可能需要查找或索引操作可能必须转换为聚集索引操作），以及“Distributor”是否可为空（在这种情况下，它可能会返回缺少值的匹配行，除了不匹配的行）。 - Aaron Bertrand

如果分销商可以为空，会怎样呢？ - Deepak Mishra

Answer 3

选择 * 从 table1 where state not in (select state from table1 where distributor = 'X')

可能不是最聪明的方法，但应该可以工作。

- Tundey

---

IN 不如 EXISTS 好用，并且无法处理状态/产品复合键。 - gbn

同样，根据关系型数据库管理系统（RDBMS）的不同，“NOT IN”在state可为空的情况下可能与预期的行为不一致。 - Aaron Bertrand

Answer 4

SELECT DISTINCT t2.State, t2.Product, t2.Version
FROM table2 t2
JOIN table1 t1 ON t1.State = t2.State AND t1.Product = t2.Product
                AND t1.Distributor <> 'X'

- Mr. Brownstone

Answer 5

在Oracle中：

SELECT t2.State, t2.Product, t2.Version
FROM Table2 t2, Table t1
WHERE t1.State(+) = t2.State
  AND t1.Product(+) = t2.Product
  AND t1.Distributor(+) = :distributor
  AND t1.State IS NULL

- Quassnoi