logo标签列表

SQL Server JOIN缺少NULL值

sqlsql-server
99

假设我有以下2个表：

      Table1:                                Table2:
Col1:      Col2:     Col3:             Col1:       Col2:       Col4:
a          b         c                 a           b           d
e          <null>    f                 e           <null>      g
h          i         j                 h           i           k
l          <null>    m                 l           <null>      n
o          <null>    p                 o           <null>      q

现在，我想通过Col1和Col2将这些表连接起来，并返回整个集合，看起来像这样：
     Result:
Col1:      Col2:     Col3:     Col4:
a          b         c         d
e          <null>    f         g
h          i         j         k
l          <null>    m         n
o          <null>    p         q

因此，我尝试了类似SQL的语句：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1
INNER JOIN Table2
    ON Table1.Col1 = Table2.Col1 
    AND Table1.Col2 = Table2.Col2

但它不能匹配Col2中的NULL值，因此最终结果为：
     Result:
Col1:      Col2:     Col3:     Col4:
a          b         c         d
h          i         j         k

我应该如何得到我想要的结果呢？

谢谢！

这个特定的问题只适用于SQL Server吗？我好像在Postgres 11.5中做不到这一点。 - Daniel Lizik
10个回答
143

您可以明确指定连接方式：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
      ON (Table1.Col1 = Table2.Col1 or Table1.Col1 is NULL and Table2.Col1 is NULL) AND
         (Table1.Col2 = Table2.Col2 or Table1.Col2 is NULL and Table2.Col2 is NULL)

实际应用中，我更倾向于在联接条件中使用coalesce()：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
     ON (coalesce(Table1.Col1, '') = coalesce(Table2.Col1, '')) AND
        (coalesce(Table1.Col2, '') = coalesce(Table2.Col2, ''))

在这两个表中都没有该值的情况下，''将是一个值。

需要注意的一点是，在大多数数据库中，使用任何这些结构都会防止索引的使用。

3
另一种coalesce的选项可能是ON (T1.C1=T2.C1 or (coalesce(T1.C1,T2.C1) is null)) and (T1.C2=T2.C2 or (coalesce(T1.C2,T2.C2) is null))。 - mpag
这种情况下无法工作：Table1 =（a，b，c）（a，null，d）Table2 =（a，b，e）。在这种情况下，连接将Table1（a，null，d）与不存在的情况Table2（a，null，null）匹配。这不应该导致匹配。 - Tripp Kinetics
换句话说，这并不区分在Table2.Col2中存在匹配的NULL和Table2中根本没有记录的情况。 - Tripp Kinetics
连接条件不应该像下面这样，在或运算符之后将空值检查作为单个单元。 (Table1.Col1 = Table2.Col1 or (Table1.Col1 is NULL and Table2.Col1 is NULL)) AND (Table1.Col2 = Table2.Col2 or (Table1.Col2 is NULL and Table2.Col2 is NULL)) - yammanuruarun
在T-SQL中，使用COALESCE()函数在JOIN操作中比明确检查NULL要慢得多。 - KurzedMetal
51

使用左连接（Left Outer Join）而不是内连接（Inner Join），以包括带有 NULL 值的行。

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1
LEFT OUTER JOIN Table2 
    ON Table1.Col1 = Table2.Col1 
    AND Table1.Col2 = Table2.Col2

更多信息请参见：http://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx

适用于我。简单而干净。 - Kuvalya
12
这并没有回答原问题。你的解决方案会在table2中包括空行，如果没有找到匹配项。请看OP问题中两个表格的第二行。 - AaA
1
这并不能真正解决问题，因为现在你必须进行过滤以选择你想要的值。为此，你又得再次处理空值。 - Jamie Marshall
4
谢谢您的好建议，这正是我所需要的。我的情况是Table.Col1为null，而Table2中根本没有空记录，因此它根本不会选择具有空col的那一行。 - Davit Mikuchadze
18

尝试使用ISNULL函数：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 
INNER JOIN Table2
   ON Table1.Col1 = Table2.Col1 
   AND ISNULL(Table1.Col2, 'ZZZZ') = ISNULL(Table2.Col2,'ZZZZ')

其中'ZZZZ'是一个任意值，从未在表中出现。

7
这里提到了魔数（Magic Values），我倾向于尽量避免使用，除非绝对必要。 - lc.
这个方案看起来很好，但是它假设在另一行中没有使用 '0'。 - PinnyM
是的，我更喜欢@Gordon的回答，只是提供了一个替代方案。而且我可能会使用一个不同的值，我知道它不能被表示为返回值，比如“ZZZZ”或其他什么 :-) - sgeddes
这并没有区分Table2.Col2 IS NULL和Table2中没有匹配记录的情况。 - Tripp Kinetics
17

简单粗暴的hack方法：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN Table2 ON Table1.Col1 = Table2.Col1
 AND ((Table1.Col2 = Table2.Col2) OR (Table1.Col2 IS NULL AND Table2.Col2 IS NULL))
谢谢，@Jap - 正是我想要的... Gordon已经回答了，但还是感谢你的回答！ - John Bustos
1
这个解决方案既不是肮脏的也不是黑客行为。 在T-SQL中，这只是正确的做法。 - David Liebeherr
1
declare @Table1 as Table(Col1 varchar(1),Col2 varchar(1), Col3 varchar(1))
declare @Table2 as Table(Col1 varchar(1),Col2 varchar(1), Col4 varchar(1))
insert into @Table1
values('a',          'b',     'c'),
('e',          null,    'f'), 
('h',          'i'  ,   'j'), 
('l',          null  ,  'm'), 
('o',          null  ,  'p') 
insert into @Table2
values('a',          'b',     'd'),
('e',          null,    'g'), 
('h',          'i'  ,   'k'), 
('l',          null  ,  'n'), 
('o',          null  ,  'q') 


select * from @Table1 tbl1
join @Table2 tbl2
on tbl1.Col1 =tbl2.Col1
and isnull(tbl1.Col2,0) =isnull(tbl2.Col2,0)

输出：

 Col1   Col2    Col3    Col1    Col2    Col4
 a      b       c       a       b       d
 e      NULL    f       e       NULL    g
 h      i       j       h       i       k
 l      NULL    m       l       NULL    n
 o      NULL    p       o       NULL    q
1
由于某些原因，我无法使用外连接使其正常工作。
所以我使用了：
SELECT * from t1 where not Id in (SELECT DISTINCT t2.id from t2)
1
你可以像这样映射。
select * from tableA a
join tableB b on isnull(a.colID,'') = isnull(b.colId,'')
这是有问题的，因为它不能区分空字符串和实际的NULL值。如果a.colD是一个空字符串而b.colID是NULL，那么比较仍然会被评估为true，即使这两列包含不同的值。在某些情况下，您不希望将NULL和空字符串视为相等。 - David Liebeherr
0
一些 SQL 实现有一个特殊的 Null-安全等于运算符。
例如，Snowflake 有 EQUAL_NULL，因此可以这样做：
SELECT 
  Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4 
FROM 
  Table1 
  INNER JOIN Table2 ON EQUAL_NULL(Table1.Col1, Table2.Col1) 
  AND EQUAL_NULL(Table1.Col2, Table2.Col2)
0

唯一正确的答案是不要将具有空值的列连接起来。这可能会很快导致意外行为。

例如：isnull(b.colId,'')：如果您的数据中有空字符串，会发生什么？连接可能会产生重复行，我想在这种情况下这并不是预期的结果。

我不同意。 正确的答案不是避免连接可空列。 正确的答案是在JOIN中正确地进行比较（正如对这个问题的其他答案所示）。 - David Liebeherr
1
你是对的David，但我认为我们可以达成共识，即ISNULL解决方案确实是一个不好的做法。 - Marcel Pfeifer
是的，使用ISNULL解决方案并不是一个好主意。 在这种情况下，使用魔术值通常会带来问题。 在此情况下使用ISNULL既不必要也不可取。NULL在T-SQL中有特殊含义。 它表示该值是未知的。 这与任何其他可能的值都非常不同。因此，SQL Server对待NULL与任何其他值的方式非常不同，一般不应试图将NULL视为任何其他值。 - David Liebeherr
0

尝试在连接中使用附加条件：

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 
INNER JOIN Table2
ON (Table1.Col1 = Table2.Col1 
    OR (Table1.Col1 IS NULL AND Table2.Col1 IS NULL)
   )
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接