July 21, 2009 at 9:57 am
I am trying to compare two columns to find the ones that are different.
If I use the following, I only get item4. I would expect to get item2, item3 and item4.
if object_id( 'tempdb..#tempa' ) is not null drop table #tempa
create table #tempa(id varchar(10),desc1 varchar(10))
insert into #tempa values('item1',NULL)
insert into #tempa values('item2',NULL)
insert into #tempa values('item3','three')
insert into #tempa values('item4','four')
insert into #tempa values('item5','five')
--
if object_id( 'tempdb..#tempb' ) is not null drop table #tempb
create table #tempb(id varchar(10),desc1 varchar(10))
insert into #tempb values('item1',NULL)
insert into #tempb values('item2','two')
insert into #tempb values('item3',NULL)
insert into #tempb values('item4','four4')
insert into #tempb values('item5','five')
--
select a.id, a.desc1, b.desc1
from #tempa a, #tempb b
where a.id = b.id
and a.desc1 <> b.desc1
What do I need to do to return all the different rows (ie, item2, item3 and item4).
Thanks... mpv
July 21, 2009 at 10:00 am
NULLs represent unknown values. So NULL is not equal to NULL because both values are unknown - how can they be equal. You need logic in your compare to handle the NULLs.
select a.id, a.desc1, b.desc1
from #tempa a, #tempb b
where a.id = b.id
and COALESCE(a.desc1,'') COALESCE(b.desc1,'')
July 21, 2009 at 10:13 am
That worked. Thanks. I have seen COALESCE before, but don't fully understand it. Is it like an OR condition where it uses the first value in the list that it can match the field to?
July 21, 2009 at 10:18 am
COALESCE searches through the list of values and returns the first non NULL value. So, SELECT COALESCE(NULL,NULL,NULL,1) would return 1. In the code I posted, when both columns contain a NULL value, you'll end up with a '' = '' comparison which results in a TRUE and therefore returns the results that you would expect.
July 21, 2009 at 10:25 am
Now I understand. Thanks for the help... and the lesson. mpv
July 21, 2009 at 1:43 pm
I would have done the following:
SELECT * FROM #tempa
EXCEPT SELECT * FROM #tempb;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply