1

I'm working on a project, I would like to know the difference between using is not null or <> null in Mysql.

Why should we use IS NOT NULL instead of <> NULL? For when I do as in the second case, no results are found.

Example

  1. Select * from table where name is not NUll;

  2. select * from table where name <> NUll;

AD7six
  • 63,116
  • 12
  • 91
  • 123
Thiago Alexandre
  • 21
  • 5
  • As already mentioned, NULL is not special value, but marker that value is missed. So you cannot compare one missing value with another. More details you can get from any SQL boot or even from wikipedia: https://en.wikipedia.org/wiki/Null_%28SQL%29 – fifonik Jan 15 '20 at 02:30
  • Does this answer your question? [Is there any difference between IS NULL and =NULL](https://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) – AD7six Nov 04 '22 at 07:05

1 Answers1

3

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

In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

NULL values represent missing unknown data.

And NULL values are treated differently from other values.

For NULL values with comparison operators, such as =, <, or <> is not possible to test for .

It's rule. So you only can use IS NULL or IS NOT NULL operators instead.

TsaiKoga
  • 12,914
  • 2
  • 19
  • 28