August 4, 2005 at 1:18 pm
Hello All,
I know this is simple but for some reason I cant get it to work.
Here is my SQL:
select d_store.store_num, d_store.location_name, d_store.store_name
from d_store,tmp_d_store where tmp_d_store.store_num = d_store.store_num and d_store.store_name <> tmp_d_store.store_name
I have a field in my table called sotre name, it may be a NULL field.
When this field is NULL I do not get any data back.
I will bet data back if I cange my SQL to say
select...where...and d_store.store_name is NULL
But putting a <> does not work.
August 4, 2005 at 1:23 pm
You are correct ... SQL Server treats nulls as special and you will often find that comparison operators (= <> etc) do not return rows where one of the items being compared is null. So you have to code for nulls explicitly, in addition to any other comparisons you have, eg:
if a <> b or a is null
is the sort of thing you're after.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 4, 2005 at 1:50 pm
If the fields on both sides of the comparison are nullable, and you want to consider NULL = NULL to be a match, you can use ISNULL on both columns with some value you're sure is not match any real data.
select ...
from ...
where tmp_d_store.store_num = d_store.store_num
and ISNULL(d_store.store_name,'xyzzy') <> ISNULL(tmp_d_store.store_name,'xyzzy')
The use of ISNULL or other functions may interfere with index usage, but in this case I would guess the tables are indexed on store_num and the ISNULL will not change the execution plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply