CODEWITHSUNDEEP
mysqlsqlselectnull
Thiago Alexandre
Thiago Alexandre

Reputation: 21

Why does "<> Null" act differently than “IS NOT NULL”?

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;

Upvotes: 1

Views: 74

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

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.

Upvotes: 3

Related Questions