NULL not matching in join

Question

I have 2 table A and B

table A

subgroup    |   maingroup   |   
------------------------------------------  
NULL        |   A       |   
NULL        |   A       |   
Top     |   B       |   
Top     |   B       |

table B

subgroup
---------------     
top             
NULL

I am running this query.

select * from a
join b
on a.subgroup=b.subgroup
group by a.subgroup,a.maingroup,b.subgroup

I am getting this output

subgroup    |   maingroup   |   subgroup    
------------------------------------------------------- 
Top     |   B       |   Top

My concern is why NULL is not matching and giving me output like NULL A Null.

I am using MSSQL

Possible duplicate of [Is there any difference between IS NULL and =NULL](http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) — jpmc26, Jun 29 '16 at 03:54

Tim Biegeleisen · Accepted Answer · 2016-06-29T06:19:50.277

6

If you want the NULL values from your two tables to match up during the join, one option is to add an explicit condition in your ON clause:

SELECT *
FROM a INNER JOIN b
    ON a.subgroup = b.subgroup OR (a.subgroup IS NULL AND b.subgroup IS NULL)
GROUP BY a.subgroup, a.maingroup, b.subgroup

The need for the explicit join condition a.subgroup IS NULL AND b.subgroup IS NULL is that comparing two NULL values for equality itself returns NULL, which is another way of saying the result is unknown.

edited Jun 29 '16 at 06:19

answered Jun 29 '16 at 03:50

Tim Biegeleisen

502,043
27
286
360

1

You beat me to it -- here's some fiddle for you: http://sqlfiddle.com/#!9/b913c1/1 – sgeddes Jun 29 '16 at 03:51
@sgeddes Appreciate you not double posting. – Tim Biegeleisen Jun 29 '16 at 03:52
No worries, didn't see your post before I submitted. Nice job. – sgeddes Jun 29 '16 at 03:52
Another way could be `ISNULL(a.subgroup,'~') = ISNULL(b.subgroup,'~')`. But this is not recommended since it will not use any index present in `subgroup` column – Pரதீப் Jun 29 '16 at 03:59
@Pdrp, Yes, I thought about coalescing the `NULL` to something else, but there is always the challenge of choosing a value which is guaranteed to never appear in the actual data. – Tim Biegeleisen Jun 29 '16 at 04:00
Perhaps it would be worth it to mention, that `NULL = NULL` evaluates neither to `TRUE` nor to `FALSE` but to `NULL`? – piet.t Jun 29 '16 at 06:18
@piet.t I added this point to my answer, thanks for the comment. – Tim Biegeleisen Jun 29 '16 at 06:20

score 1 · Answer 2 · edited Jun 29 '16 at 06:13

1

If you want the NULL values from your two tables to match up during the join, you can use the IsNull function.

SELECT *
FROM a INNER JOIN b
    ON IsNull(a.subgroup,'') = IsNull(b.subgroup,'')
GROUP BY a.subgroup, a.maingroup, b.subgroup

edited Jun 29 '16 at 06:13

Fred Sobotka

5,252
22
32

answered Jun 29 '16 at 06:07

M. Jamshaid Alam

86
9

Nazir Ullah · Answer 3 · 2016-06-29T06:16:47.620

0

Simple try with ISNULL function as

SELECT *   FROM a INNER JOIN b

ON ISNULL(a.subgroup,'') = ISNULL(b.subgroup,'')

GROUP BY a.subgroup, a.maingroup, b.subgroup

edited Jun 29 '16 at 06:16

answered Jun 29 '16 at 06:04

Nazir Ullah

610
5
13

Assuming that you want to treat `NULL`s as `0`s or that `0` (or a different value that should be substituted) can act as a sentinel value that doesn't exist in the actual data. – Damien_The_Unbeliever Jun 29 '16 at 06:15

NULL not matching in join

3 Answers3