Comparing 2 Queries - SQL Server Forums

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

Other Forums

MS Access

Comparing 2 Queries

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2009-06-08 : 23:01:14

Hey guys,

I'm having a really hard time understanding why the following 2 queries produce different results (please see the end of this explanation for the 2 queries).

I am comparing 2 tables: ITEM_2 vs. ITEM_3. The tables are essentially the same with the exact same number of columns, column names, etc. ITEM_2 has been updated but ITEM_3 has not. I simply did a field for field comparison of the 2 tables and extracted whatever is not the same, representing a change or update in the ITEM_2 table.

What I am not understanding is why query #1 does not catch a record that has a NULL value in one table but not the other. i.e. ITEM_DESCR field in ITEM_3 is NULL but ITEM_DESCR in ITEM_2 is populated with a value. The not equal to condition (<>) in the WHERE clause in query #1 should satisfy this criteria. If I add extra OR conditions in the WHERE clause of query #2 to compare NULL values then the record is extracted as expected.

If a field is NULL in one table but NOT NULL in another table, then does this not mean that they are NOT EQUAL? Query #1 should be enough code to extract the exact same records in Query #2. Why is this happening??

Query #1
---------------------------------------------------------------------
SELECT ITEM_2.ITEM_NO, ITEM_2.ITEM_REF_NO, ITEM_2.DESCR, ITEM_2.DESCR1,
ITEM_2.DESCR2
FROM ITEM_2, ITEM_3
WHERE ITEM_2.ITEM_NO = ITEM_3.ITEM_NO
AND
(
ITEM_2.DESCR <> ITEM_3.DESCR
OR ITEM_2.DESCR1 <> ITEM_3.DESCR1
OR ITEM_2.DESCR2 <> ITEM_3.DESCR2
)
;

Query #2:
---------------------------------------------------------------------
SELECT ITEM_2.ITEM_NO, ITEM_2.ITEM_REF_NO, ITEM_2.DESCR, ITEM_2.DESCR1,
ITEM_2.DESCR2
FROM ITEM_2, ITEM_3
WHERE ITEM_2.ITEM_NO = ITEM_3.ITEM_NO
AND
(
ITEM_2.DESCR <> ITEM_3.DESCR
OR ITEM_2.DESCR1 <> ITEM_3.DESCR1
OR ITEM_2.DESCR2 <> ITEM_3.DESCR2

OR

(
ITEM_2.DESCR IS NULL AND ITEM_3.DESCR IS NOT NULL)
OR (ITEM_2.DESCR1 IS NULL AND ITEM_3.DESCR1 IS NOT NULL)
OR (ITEM_2.DESCR2 IS NULL AND ITEM_3.DESCR2 IS NOT NULL
)
OR
(
ITEM_3.DESCR IS NULL AND ITEM_2.DESCR IS NOT NULL)
OR (ITEM_3.DESCR1 IS NULL AND ITEM_2.DESCR1 IS NOT NULL)
OR (ITEM_3.DESCR2 IS NULL AND ITEM_2.DESCR2 IS NOT NULL
)
)
;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 04:40:42

Because NULL means that column has no value defined, it cannot be compared with another value.
Only thing possible is to compare if a column is null or a column is not null.
select 1 where NULL <> 47
is not true, but you can do
select 1 where isnull(NULL,'') <> 47

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2009-06-09 : 21:21:35

Yeah, I had a feeling that it was going to be something with the NULL values. So query #2 is what I definitely need in order to extract a more accurate report.

Thanks WebFred, it's "clear as mud now"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources