SQL Server JOIN missing NULL values

Question

Suppose I had the following 2 tables:

      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

Now, I want to join these tables on Col1 and Col2 and bring back the entire set to look like:

     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

So, I tried a SQL like:

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

But it isn't matching the NULL values in Col2, so I end up with:

     Result:
Col1:      Col2:     Col3:     Col4:
a          b         c         d
h          i         j         k

How can I get the result I am looking for??

Thanks!

is this specific to sql-server? i can't seem to do this in postgres 11.5 — Daniel Lizik
– Daniel Lizik, Commented Jan 17, 2020 at 3:37

Gordon Linoff · Accepted Answer · 2016-05-11 01:41:15Z

152

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.

edited May 11, 2016 at 1:41

answered Jan 16, 2013 at 19:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mpag Over a year ago

another option for coalesce would be 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))

Tripp Kinetics Over a year ago

This doesn't work in the case where you have Table1=(a,b,c)(a,null,d) Table2=(a,b,e). In this case, the join matches the case Table1(a,null,d) with the nonexistent case Table2(a,null,null). This should not result in a match.

Tripp Kinetics Over a year ago

In other words, this does not distinguish between cases where there is a NULL in Table2.Col2 which matches, versus no record at all in Table2.

yammanuruarun Over a year ago

Shouldn't join conditions be like below with null checks as a single unit after or operator. (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))

KurzedMetal Over a year ago

In T-SQL, using COALESCE() in a JOIN is way slower than explicitly checking NULL

dwitvliet · Accepted Answer · 2022-08-24 13:55:33Z

53

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

edited Aug 24, 2022 at 13:55

dwitvliet

7,7317 gold badges40 silver badges66 bronze badges

answered Jul 15, 2013 at 19:38

Dave Hackett

5954 silver badges2 bronze badges

3 Comments

AaA Over a year ago

This does not answer the original question. your solution will include null rows for table2 if no match found. look at second row in both tables in OP's question

Jamie Marshall Over a year ago

This doesn't really solve the question as you'd now have to filter to select the values you want. To do that you'll have contend with the nulls again.

Davit Mikuchadze Over a year ago

Thanks for good advice, thats what i have needed. My case was Table.Col1 was null and there was no null record in Table2 at all, so it wasn't select that row with null col at all.

jap1968 · Accepted Answer · 2013-01-16 19:19:56Z

18

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

answered Jan 16, 2013 at 19:19

jap1968

7,7631 gold badge33 silver badges37 bronze badges

1 Comment

David Liebeherr Over a year ago

This solution is neither dirty nor a hack. It is simply the correct way to do it in T-SQL.

wickedone · Accepted Answer · 2013-02-15 18:47:44Z

18

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.

edited Feb 15, 2013 at 18:47

wickedone

5401 gold badge6 silver badges18 bronze badges

answered Jan 16, 2013 at 19:17

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

5 Comments

lc. Over a year ago

This introduces magic values which I tend to avoid unless absolutely necessary

PinnyM Over a year ago

This would work nicely, but assumes that '0' isn't used elsewhere in another row.

sgeddes Over a year ago

Yeah, I prefer @Gordon's reply, just offering an alternative. And I'd probably use a different value that I knew couldn't be represented in the return values like 'ZZZZ' or something :-)

Tripp Kinetics Over a year ago

This doesn't distinguish between cases where Table2.Col2 IS NULL versus cases where there is no matching record in Table2.

Simon_Weaver Apr 6 at 20:36

Frankly you can probably get away with '' instead of 'zzzz' for 99% of the time you find yourself needing to do something like this. Otherwise what you're saying is that an empty string is NOT equivalent to a NULL (for the purpose of doing the join), which seems odd. For my use-case I am comparing addresses and sometimes AddressLine2 is null, but that's logically equivalent to an empty string for an address. Of course, only you know your data - and it's important to be careful about making quick decisions and assumptions with things like this that might bite you later.

wiretext · Accepted Answer · 2015-07-04 13:20:08Z

1

you can just map like that

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

answered Jul 4, 2015 at 13:20

wiretext

3,35216 silver badges19 bronze badges

1 Comment

David Liebeherr Over a year ago

This is problematic, because it doesn't differentiate between an empty string an an actual NULL value. In case a.colD is an empty string and b.colID is NULL the comparison would still evaluate to true, even though the two columns contain different values. In some cases you don't want to treat NULL and an empty string as being equal.

user11333280 · Accepted Answer · 2019-04-09 08:13:37Z

1

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)

answered Apr 9, 2019 at 8:13

user11333280

111 bronze badge

Comments

ListenSoftware Louise Ai Agent · Accepted Answer · 2021-07-07 16:05:20Z

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

Fabich · Accepted Answer · 2021-10-27 10:19:46Z

1

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)

answered Oct 27, 2021 at 10:19

Fabich

3,1354 gold badges35 silver badges48 bronze badges

Comments

Zeina · Accepted Answer · 2019-12-17 13:10:26Z

0

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

edited Dec 17, 2019 at 13:10

Zeina

1,5912 gold badges25 silver badges34 bronze badges

answered Dec 17, 2019 at 12:46

Danny Costin

111 bronze badge

Comments

Marcel Pfeifer · Accepted Answer · 2020-08-03 07:17:07Z

0

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.

answered Aug 3, 2020 at 7:17

Marcel Pfeifer

314 bronze badges

3 Comments

David Liebeherr Over a year ago

I disagree. The correct answer is not to avoid joining nullable columns. The correct answer is to do the comparison in the JOIN correctly (as demonstrated in the other answers to this question).

Marcel Pfeifer Over a year ago

You are right David, but I think that we can agree on the fact that the ISNULL solution is really bad practice.

David Liebeherr Over a year ago

Yes, the ISNULL solution is not a good idea. Using magic values is generally problematic, especially in this situation. Using ISNULL in this situation is neither necessary nor advisable. NULL does have a special meaning in T-SQL. It means that the value is unknown. That is very different from any other possible value. Therefore SQL Server treats NULL very differently than any other value and one should generally not attempt to treat NULL the same as any other value.

ocnerF · Accepted Answer · 2025-08-21 14:52:08Z

-1

I think that if you have the possibility to change the schema table then you could modify the tables columns used in the join condition appending a default value like ''. so you can use the nomal join condition and index will be used.

answered Aug 21 at 14:52

ocnerF

462 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Server JOIN missing NULL values

11 Answers 11

5 Comments

3 Comments

1 Comment

5 Comments

1 Comment

Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

5 Comments

3 Comments

1 Comment

5 Comments

1 Comment

Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related