NULL Values in SQL Views 1

[Aidy680]

Hi,

I have a View that links 2 tables on Field A in table 1 and field B in table 2. They are the same datatype.

I want to return records where they DONT equal each other.

This works fine when comparing actual data, however if NULL exists in one field, then this record isnt returned as an exception, even though the 2 fields aren't equal to each other.

I know that SQL handles NULLs differently to Access but surely this cant be that difficult!

 

[pjw001]

No it isn't that difficult, it's just that dealing with NULLs can be a bit of a pain.

What I think that you need is:

where A <> B
or (A is NULL and B is NOT NULL)
or (A is NOT NULL and B is NULL)

There are other, probably better, ways of dealing with nulls, but I think that this will work for you.

 

[Aidy680]

Thanks pjw001 I'll give it a whirl.

 

[Rhys666]

...or...

WHERE ISNULL(A, A_DEFAULT_VALUE) <> ISNULL(B, A_DIFFERENT_DEFAULT_VALUE)

i.e., 
WHERE ISNULL(A, 1) <> ISNULL(B, 2)

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[pjw001]

Rhys666's answer will not give the same result as mine as it will assume that if both are null there is no match.

Mine would treat two null values as matching.

Which the correct answer is depends on what Aidy680 wants.

 

[Rhys666]

pjw001 is logically correct, however as a NULL is an unknown value, two NULL values never, strictly speaking match.

Realistically two NULLS may or may not be a match depending on your own schema and data definition(s).

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Aidy680]

I used pjw001's solution and it's worked a treat.

Although I accept that, strictly speaking, 2 NULLS are unknown values, the reality is that in such a situation, I'd want them to be flagged.

Thanks guys for your input.