SQL Comparison operations ( =, <>, !=, <, >, <=, >=) with NULL

I had question about what would be returned from this query

SELECT `name` FROM `customer` WHERE `date` <> '05/05/10'

Here was the table:

John Smith null
Mary Smith 05/05/10
Joe Allen 02/16/10

I had to take a pause at that. I had no idea if John Smith would be returned.

Turns out that NULL is never equal to another value.
It is also never not equal to another value.
NULL is basically like “I have no idea” when it comes to comparisons.

The way to return both John Smith and Joe Allen would be

SELECT `name` FROM `customer` WHERE `date` <> '05/05/10' OR `date` IS NOT NULL


Leave a Reply