当两个表都包含NULL时如何使用JOIN ON

Question

我正在对两个表格的两列进行外连接。当table1.column1=table2.column1 和 table1.column2=table2.column2时，才进行连接。因为column2允许包含null值，所以每当该值为null时，连接失败，因为null不等于null（只有计算机科学家可能喜欢这一点）。

我想到的解决方法是：

select table1.column1,table1.colunn1,table2.column1,table2.column2 from 
table1 
left join table2 
       on table1.column1=table2.column1 
       and if(table1.column2 is null,table2.column2 is null, table1.column2=table2.column2)

这个方法可以正常工作，但肯定有更好的办法吧？

- Sam Goldberg

---

你不必是计算机科学家才能理解为什么 NULL = NULL 不能真正导致 TRUE。只需考虑以下示例。《哈姆雷特》的作者是莎士比亚，而《麦克白》的作者也是莎士比亚。另一方面，《吉尔伽美什史诗》的作者是未知的（NULL），《摩诃婆罗多》的作者也是如此。现在，我们可以说前两本书的作者是同一个人，但我们不能这样说另外两本书。 - Andriy M

@AndriyM：说得好。虽然显然针对数据库使用，但许多人并不真正考虑他们的数据 - 因此需要mysql包括一个空值安全比较运算符。我个人认为应该将其作为默认设置，并让人们明确选择严格的空值运算符。 - Sam Goldberg

在提供您的规范时，您并没有明确说明何时使用普通等式和何时使用SQL“等式”。您可以使用“is”表示普通等式。另一个问题是，普通（数学和计算机科学）术语“value”也被SQL修辞所占用，其中模糊且无用的声明声称SQL null不是值。当然它是一个值，只是被SQL运算符像SQL“等式”一样特殊处理。 - philipxy

Answer 1

您可以使用MySQL的null-safe比较运算符<=>:

SELECT    t1.column1, t1.column2, t2.column1, t2.column2 
FROM      table1 t1
LEFT JOIN table2 t2 
       ON t1.column1 = t2.column1 AND t1.column2 <=> t2.column2

- Zane Bien

---

难道不应该只是t1.column2 <=> t2.column2吗？一旦你使用了这个比较，另一个就变得多余了，除非我漏掉了什么（至少很少用<=>）。 - Andriy M

@AndriyM，OP想要在两个不同的列上进行连接：column1和column2。 column2可以包含NULL，但我们仍然希望保留对column1的连接条件。 - Zane Bien

啊，是的，这些列不同，我没注意到。抱歉。 - Andriy M

我之前不知道有空安全比较运算符。这正是我所需要的，而且它完美地运行了，谢谢！ - Sam Goldberg

Answer 2

我会执行 LEFT JOIN table2 ON table1.column1 = table2.column1 OR (table1.column1 IS NULL AND table2.column1 IS NULL)。不确定是否有效。

(顺便说一下，null 不是值。)

- Jason Swett

Answer 3

由于源数据中存在NULL，在使用LEFT JOIN之前，请使用COALESCE（或Oracle中的NVL）默认值，以便WHERE子句按预期工作。根据数据类型，'DEFAULT_VALUE'可以是任何您选择的值。这种方法非常有效，因为它消除了混淆，并允许表达式正常工作，而不会被NULL阻碍。

使用内联视图：

select table1.column1,table1.column1,table2.column1,table2.column2
from
(
    select
    table1.column1
    coalesce(table1.column2,'DEFAULT_VALUE') as column2
    from table1
) t1
left join (
            select
            table2.column1
            coalesce(table2.column2,'DEFAULT_VALUE') as column2
            from table2
          ) t2
       on t2.column1 = t1.column1
      and t2.column2 = t1.column2;

或者使用通用表达式：

with t1 as
(
    select
    table1.column1
    coalesce(table1.column2,'DEFAULT_VALUE') as column2
    from table1
),
t2 as
(
    select
    table2.column1
    coalesce(table2.column2,'DEFAULT_VALUE') as column2
    from table2
)     
select table1.column1,table1.column1,table2.column1,table2.column2
from t1
left join t2
       on t2.column1 = t1.column1
      and t2.column2 = t1.column2;  

- bruceskyaus