Why is this query returning 0 rows?
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1<>t.f2;
This is a distilled version of a complex query I have. I want to compare two tables containing one-to-one related data and I want to select those rows that contain different values for certain fields. But also there can be the case where one row is missing in one of the tables. The LEFT JOIN correctly returns null values for these rows, but then, the WHERE clause is incorrectly (or unexpectedly) filtering these rows out.
Why -in this case- 'null' IS NOT DIFFERENT to any not null value (like 'a') ?
What is driving me crazy is that this
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t;
returns 1 row (as I expected) but this
select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1=t.f2;
returns 0 rows !! So null is not equal to 'a' and null is not different to 'a' !!
Please... Can anybody explain this?