当您希望NULL = NULL为true时，在SQL中应该怎么做？

Question

假设有一张表（名为Example），它有一个主键字段（KeyID）和一个外键字段（ForeignID）。这个外键用于将我们表中的行与另一张表中的行/项链接起来，建立一对多的关系。然而，对于我们的许多行，不存在这样的关系，所以这些行的外键字段是NULL。

现在，如果我们有一个KeyID（例如123），那么获取包含所有具有匹配的ForeignID值的行的结果集的首选SQL语句是什么？

我天真地从以下SQL开始：

SELECT E1.*
FROM Example E1
JOIN Example E2
  ON E2.KeyID = 123
  AND E2.ForeignID = E1.ForeignID

当我们的匹配键行在ForeignID中具有正常值时，这段代码运行得非常好。但是，如果ForeignID恰好为NULL，它将失败（返回空值）。经过一些初步搜索，我现在明白了原因（在阅读了像这样的问题之后），但我还没有找到任何很好的解决方案来解决这个限制。

SQL Server提供了ANSI_NULLS设置，可以更改该设置，但这似乎是一种肮脏的，潜在有问题的方式。

另外，我可以始终使用自己的伪空值（如0）并将其插入ForeignID列而不是NULL，但这将破坏我为此列建立的外键约束。

那么，最好的方法是什么？

- Jeremy

Answer 1

虽然未经测试，但我认为您可以这样做：

SELECT E1.*
FROM Example E1
JOIN Example E2
  ON E2.KeyID = 123
  AND (E2.ForeignID = E1.ForeignID
    OR (E2.ForeignID IS NULL AND E1.ForeignID IS NULL))

编辑：在这里可以使用，SQL Fiddle链接

- Jeff Rosenberg

---

你可能应该加上一些括号，以使你的操作更明显，同时让 OP 和 SQL Server 都能理解。 - Blorgbeard

答案很简单，易于理解和明显，所以当然我没想到。-_- - Jeremy

请注意，这将导致从E1的NULL行与E2的NULL行进行笛卡尔积。只要其中一个表中有一个带有NULL的行，您就应该没问题了。 - N West

@Blorgbeard，“a OR b AND c”不需要额外的括号，它总是表示“a OR (b AND c)”。对于OP来说可读性是一个公正的观点，但对于SQL Server来说可读性则不是。 - user743382

Answer 2

您也可以这样做

SELECT E1.*
FROM   Example E1
       JOIN Example E2
         ON E2.KeyID = 123
            AND EXISTS (SELECT E2.ForeignID
                        INTERSECT
                        SELECT E1.ForeignID) 

- Martin Smith

---

虽然我使用的一些数据库中没有INTERSECT，但我开始意识到它有多么有用。这个答案还引导我找到了以下有用的博客：未记录的查询计划：相等比较。 - Jeremy