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

Question

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?

Use column3 IS NULL to check for null value.

jarlh
– jarlh

2017-09-05 07:33:21 +00:00
Commented Sep 5, 2017 at 7:33 — jarlh
– jarlh, Commented Sep 5, 2017 at 7:33
An option is <=> NULL-safe equal to operator.

wchiquito
– wchiquito

2017-09-05 07:48:05 +00:00
Commented Sep 5, 2017 at 7:48 — wchiquito
– wchiquito, Commented Sep 5, 2017 at 7:48

Radim Bača · Accepted Answer · 2017-09-06 04:50:58Z

3

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

edited Sep 6, 2017 at 4:50

answered Sep 5, 2017 at 7:51

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cleverpaul Over a year ago

Great explanation!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related