CODEWITHSUNDEEP
sqlsql-serverjoin
Red Devil
Red Devil

Reputation: 2403

NULL not matching in join

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

Upvotes: 2

Views: 132

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

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.

Upvotes: 6

Nazir Ullah
Nazir Ullah

Reputation: 610

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

Upvotes: 0

M. Jamshaid Alam
M. Jamshaid Alam

Reputation: 86

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

Upvotes: 1

Related Questions