SQLServerJOIN 缺少 NULL 值

假设我有以下两个表:

      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

我怎样才能得到我想要的结果? ？

谢谢！

205067 次浏览
小开
最佳答案

You can be explicit about the joins:

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)

In practice, I would be more likely to use coalesce() in the join condition:

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, ''))

Where '' would be a value not in either of the tables.

Just a word of caution. In most databases, using any of these constructs prevents the use of indexes.

小开

Try using ISNULL function:

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')

Where 'ZZZZ' is some arbitrary value never in the table.

小开

Dirty and quick 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))
小开

Use Left Outer Join instead of Inner Join to include rows with NULLS.

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

For more information, see here: http://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx

小开

you can just map like that

select * from tableA a
join tableB b on isnull(a.colID,'') = isnull(b.colId,'')
小开

for some reason I couldn't get it to work with the outer join.

So I used:

SELECT * from t1 where not Id in (SELECT DISTINCT t2.id from t2)
小开

Try using additional condition in join:

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)
)
小开

The only correct answer is not to join columns with null values. This can lead to unwanted behaviour very quickly.

e.g. isnull(b.colId,''): What happens if you have empty strings in your data? The join maybe duplicate rows which I guess is not intended in this case.

小开 
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)

output:

 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
小开

Some SQL implementations have a special Null-safe equal operator.

For instance Snowflake has EQUAL_NULL so ou can do

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)

提交答案