Mysql mystery: null value is not different to non-null string
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 wan't 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?

Mysql mystery: null value is not different to non-null string
===

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 wan't 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?
