Query two fields on same table including NULL

[JMay0816]

I have an interesting item here.

I am trying to pull records from a table by comparing two fields (a.Field1 <> a.Field2) It works fine when both fields have actual data. However, there are several records where Field2 is NULL.

Is there a more efficent way of coding the WHERE clause without using an OR statment?

I.e.
From tbl a, tbl b
WHERE a.recn = b.recn AND
a.Field1 <> a.Field2

Thx

 

[flubbard]

What happens when a null is encountered. If the <> fails, do something like:

From tbl a, tbl b 
WHERE a.recn = b.recn AND
a.Field1 <> a.Field2 AND
NOT a.Field2 IS NULL

Just a thought.

- flub

 

[DonQuichote]

This is due to a language "feature" called Null-propagation. What you experience is that booleans operators are NOT booleans themselves, as they can return NULL instead of TRUE or FALSE. And if one of the operand is NULL, they return NULL. This off course breaks many boolean rules.

So you have to check for NULL explicitly:

From tbl a, tbl b
WHERE a.recn = b.recn AND
((a.Field1 <> a.Field2) OR (a.Field1 IS NULL AND a.Field2 IS NOT NULL) OR (a.Field1 IS NOT NULL AND a.Field2 IS NULL)

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)