Joining Tables on Null able Columns

Is Null=Null. No, Null never equal to null because null values represent to unknown value so two unknown value will not be same. Let’s see below example

We have two table

CREATE TABLE tbl1   (      nm VARCHAR(50) NULL   );   CREATE TABLE tbl2   (      nm VARCHAR(50) NULL   ); --inserting the records into both table insert into tbl1 values      ('Bagesh'),             ('Rajesh'),             (null),             (null); insert into tbl2 values   (null),              (null),             ('Bagesh'),             ('Mohan'),             (null);

 Let’s see if we are joining both table

select * from   tbl1 t1        join tbl2 t2          on t1.nm = t2.nm; select * from   tbl1 t1        left join tbl2 t2               on t1.nm = t2.nm; select * from   tbl1 t1        right join tbl2 t2                on t1.nm = t2.nm;

See the result below.

See in the above example null=null not evaluate. And in inner join it will return only matching records only. If we want to return the null value we need to replace null value with some other value using Isnull function or coalesce function. See the below example.

Read: Different ways to replace NULL valuein sql server

https://bageshkumarbagi-msbi.blogspot.com/2015/12/different-ways-to-replace-null-in-sql.html

select * from   tbl1 t1        join tbl2 t2          on Isnull(t1.nm, '99999') = Isnull(t2.nm, '99999') select * from   tbl1 t1        left join tbl2 t2               on Isnull(t1.nm, '99999') = Isnull(t2.nm, '99999') select * from   tbl1 t1        right join tbl2 t2                on Isnull(t1.nm, '99999') = Isnull(t2.nm, '99999')

See the result