Mysql Select Rows Where two columns do not have the same value

Question

I'm trying to run a query where two columns are not the same, but it's not returning any results:

SELECT * FROM `my_table` WHERE `column_a` != `column_b`;

column_a AND column_b are of integer type and can contain nulls. I've tried using <> IS NOT, etc without any luck. It's easy to find if they're the same using <=>, but <> and != doesn't return any rows. (using Mysql 5.0).

Thoughts?

Answer 1

The problem is that a != b is NULL when either a or b is NULL.

<=> is the NULL-safe equals operator. To get a NULL-safe not equal to you can simply invert the result:

SELECT *
FROM my_table
WHERE NOT column_a <=> column_b

Without using the null safe operator you would have to do this:

SELECT *
FROM my_table
WHERE column_a != column_b
OR (column_a IS NULL AND column_b IS NOT NULL)
OR (column_b IS NULL AND column_a IS NOT NULL)