mySQL select query does not return results when using a WHERE clause against a NULL column.

I have a single column in a table which looks like this:

(field3 is NULL. I use QTODBC7.0 to perform SQL queries, and this application makes NULL fields appear blank in the query results, rather than displaying the text ‘NULL’)

I have a simple SQL query as follows:

select * from table where field3 <> 'randomstring'

I expect the query to return ALL fields where field3 does not equal the value of ‘randomstring’.

The column, ‘field3’ does not equal the value of ‘randomstring’ therefore, it should be returned in the query.

A strange thing happens:

The above query returns nothing!

field3 equals NULL therefore, it does not equal ‘randomstring’ therefore, the row should be returned in my query, but it is not.

I did some testing, and discovered, this is caused when field3 is NULL but not If field3 equals a blank string (not NULL).

How can I make the select statement work when there could be NULL values in field3?

Answer

This behavior is caused by a three-value logic that is part of SQL standard. It means that every boolean condition can be evaluated to true, false or unknown and the row is returned only when the condition is true. It is defined that if there is a NULL value on one side of the equation (or both) then the result is unknown. In your case, the result of the condition is unknown when field3 is NULL. Therefore you need to use a solution proposed by jarlh:

SELECT * FROM table WHERE field3 <> 'randomstring' or field3 IS NULL

In such query the condition is evaluated to true if the value of field3 is NULL since unknown or true = true