This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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) |
