MySQL Query returning "Empty Set", Why?

Question

Why does the following query return an empty set?

SELECT * 
   FROM null_test_tab 
   WHERE col1 = NULL
   ORDER BY id

Result:

Empty set

jspcal · Accepted Answer · 2019-02-06 03:42:13Z

5

The expression should be col is null. The result of an arithmetic comparison with null, such as col = null, is null.

answered Feb 6, 2019 at 3:42

jspcal

52k7 gold badges76 silver badges79 bronze badges

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

Comments

Ramlal S · Accepted Answer · 2019-07-02 13:49:45Z

2

Try this:

 SELECT * 
 FROM null_test_tab 
 WHERE col1 IS NULL ORDER BY id

Here,NULL means “a missing unknown value”.

To test for NULL, use the IS NULL and IS NOT NULL operators.

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

For more details check the following from mySQL doc

answered Feb 6, 2019 at 3:50

Ramlal S

1,6531 gold badge18 silver badges36 bronze badges