Comparing with NULL value is not working

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

SQL Server 2012 Forums

Transact-SQL (2012)

Comparing with NULL value is not working

Author

Topic

Srinivas007
Starting Member

7 Posts

Posted - 2013-06-03 : 07:05:10

Hi All,

I used a query to compare the data between 2 tables. The query was comparing the data and giving the expected results except for the NULL data.

For example, if table1 contain 'ABC' for column1 and table2 contain NULL for column1, 'ABC' is not getting compared with 'NULL' and couldn't get the expected result.

My query will give the results set of only the mismatch data in any column between 2 tables.

Please find below my query and anyone could help me in comparing the NULL values which should fit in my below query.

select AccountId, AccountName, Address into #temp1 from Table1

select AccountId, AccountName, Address into #temp2 from Table2

--Compare

select diff.MismatchColumns, t1.*, t2.* into ##temp
from #temp1 t1
left outer join #temp2 t2 on (t2.AccountId = t1.AccountId)
cross apply (
select stuff((
select ', ' + t.name as [text()]
from (
select 'AccountID' as name where t1.AccountId is null or t2.AccountId is null
union all select 'AccountName' where not t1.AccountName = t2.AccountName
union all select 'Address' where not t1.Address = t2.Address) t
for xml path(''), type
).value('.','varchar(8000)'),1, 2, '') as MismatchColumns
) diff
where diff.MismatchColumns is not null

Thanks
Srinivas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 07:11:34

i cant see where you're doing comparison between the columns using NULLs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Srinivas007
Starting Member

7 Posts

Posted - 2013-06-03 : 07:22:46

In the above example:

Some of the Accounts have address in table1 and don't have data in table2. In this case, the query is giving the results as data matched but it is not the actual result. How do I compare a value in table1 with NULL value in table 2 within my query to get mismatched data?

The query is picking up if there is any data mismatch and giving the results accordingly but it is not comparing with NULL value.

Thanks
Srinivas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 07:26:06

use

isnull(t1.AccountId,'') = isnull(t2.AccountId,'')

etc for comparison including NULL value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Srinivas007
Starting Member

7 Posts

Posted - 2013-06-03 : 07:39:17

It worked for me...Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 07:41:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources