1

Why does the following query return an empty set?

SELECT * 
   FROM null_test_tab 
   WHERE col1 = NULL
   ORDER BY id

Result:

Empty set
Stefan Becker
  • 5,695
  • 9
  • 20
  • 30
Maylat Temasgane
  • 31
  • 1
  • 5
  • 2
    Possible duplicate of [SQL is null and = null](https://stackoverflow.com/questions/9581745/sql-is-null-and-null) – Andreas Feb 06 '19 at 03:43

2 Answers2

5

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

Take a look at: https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

jspcal
  • 50,847
  • 7
  • 72
  • 76
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

https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

Ramlal S
  • 1,573
  • 1
  • 14
  • 34