SQL Server: Never Apply INNER JOIN or EQUI JOIN on NULL Values

 

Sharing the truth about NULL Values when we using an INNER JOIN or EQUI JOIN of SQL Server. Only a few of the good DB Developers know all the truth behind the NULL.

I already shared a couple of articles on NULL. When we are joining two NULL values, we can’t get any result out of it because NULL is not a value which is just an empty identifier. So try to avoid NULL columns in the INNER JOIN or EQUI JOIN or you can add IS NOT NULL condition.

Please check below few samples:
Create sample tables:

1 2 3 4 5 6

CREATE TABLE tbl_a1 (ID INT, Name VARCHAR(5)) CREATE TABLE tbl_a2 (ID INT, Name VARCHAR(5))   INSERT INTO tbl_a1 VALUES (1,NULL),(2,NULL) INSERT INTO tbl_a2 VALUES (1,NULL),(2,NULL) GO

NULL = NULL: No result

1 2 3 4 5 6 7 8 9

SELECT a.* FROM tbl_a1 as a INNER JOIN tbl_a2 as b on a.Name = b.Name   ID          Name ----------- -----   (0 row(s) affected)

RIGHT OUTER NULL: All records NULL

1 2 3 4 5 6 7 8 9 10 11

SELECT a.* FROM tbl_a1 as a RIGHT OUTER JOIN tbl_a2 as b on a.Name = b.Name   ID          Name ----------- ----- NULL        NULL NULL        NULL   (2 row(s) affected)

Join on ID: Expected Result

1 2 3 4 5 6 7 8 9 10 11

SELECT a.* FROM tbl_a1 as a INNER JOIN tbl_a2 as b on a.id = b.id   ID          Name ----------- ----- 1           NULL 2           NULL   (2 row(s) affected)

LEFT OUTER NULL: Expected Result of for tbl_a1

1 2 3 4 5 6 7 8 9 10 11

SELECT a.* FROM tbl_a1 as a LEFT OUTER JOIN tbl_a2 as b on a.Name = b.Name   ID          Name ----------- ----- 1           NULL 2           NULL   (2 row(s) affected)