Compare NULL = NULL in LEFT JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compare NULL = NULL in LEFT JOIN

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-08-08 : 05:35:35

Dear Friends,

I am having two table, please find the temp table creation below.

Create table #temp1(Name Varchar(50), Address1 VARCHAR(100) NULL,Address2 VARCHAR(100))

INSERT INTO #temp1(Name,Address1) VALUES('Hi','CHENNAI')
GO
INSERT INTO #temp1(Name,Address1) VALUES('Hello','TVM')
GO
INSERT INTO #temp1(Name,Address1) VALUES('Haiii','TN')
SELECT * FROM #temp1

Create table #temp2(Name Varchar(50), Address1 VARCHAR(100) NULL,Address2 VARCHAR(100))
INSERT INTO #temp2(Name,Address1) VALUES('Hi','CHENNAI')
GO
INSERT INTO #temp2(Name,Address1) VALUES('Hello','TVM')
GO
INSERT INTO #temp2(Name,Address1) VALUES('Haiii','TN')
SELECT * FROM #temp2

I am using the below Query,

SELECT B.* FROM #temp1 A
LEFT JOIN #temp2 B
ON A.Name=B.Name AND
A.Address1=B.Address1
AND
A.Address2=B.Address2

Here if i try to compare the A.Address2 and B.Address2 , it is not returning any values.

I want to check NULL = NULL in LEFT JOIN, Please let me know how to modify my Query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 05:41:49

you cant check NULL=NULL as NULL is not stored as a value under default condition. for that you need to change ANSI NULLs setting or make condition as


SELECT B.* FROM #temp1 A
LEFT JOIN #temp2 B
ON A.Name=B.Name AND
A.Address1=B.Address1 
AND
(A.Address2=B.Address2
OR (A.Address2 is null and B.Address2 is null ))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-08-08 : 05:43:29

SELECT B.* FROM #temp1 A
LEFT JOIN #temp2 B
ON A.Name=B.Name AND
A.Address1=B.Address1
AND
ISNULL(A.Address2,'')=ISNULL(B.Address2,'')

Although this would never use any indexes, so be careful with its use and chedck your performance before using it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-08 : 07:36:31

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/12/24/null-on-joined-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources