Difference Between MySql <> NULL and IS NOT NULL

Question

In my db there is a varchar(255) column and for some records it contains null values, when i fired this

SELECT * FORM my_table where some_column <> NULL;

nothing is returned

but when is fired

SELECT * FORM my_table where some_column IS NOT NULL;

I got desired records

can you explain what's the main difference between them and when to use <> and != operators.

The second is the correct, documented, and standard way to compare a value to NULL in SQL. For all practical purposes, the first is a typo. — Gordon Linoff
– Gordon Linoff, Commented Jan 25, 2019 at 12:46
NULL <> NULL is NULL, not true. Comparing NULL to any other value including NULL is always NULL and therefore not true, regardless of the comparison operator, unless it is IS NULL or IS NOT NULL. — sticky bit
– sticky bit, Commented Jan 25, 2019 at 12:47
Additional detail: some_column = NULL always returns false. Also some_column <> NULL always returns false. — Robert Kock
– Robert Kock, Commented Jan 25, 2019 at 12:49
@RobertKock - no it doesn't - it returns UNKNOWN/NULL. If your assertion was correct NOT (some_column = NULL) would be true. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 25, 2019 at 12:50
@Damien_The_Unbeliever I didn't know that. Forget my comment. — Robert Kock
– Robert Kock, Commented Jan 25, 2019 at 12:52

Juan · Accepted Answer · 2019-01-25 12:58:43Z

6

From the Mysql 8 Reference:

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

answered Jan 25, 2019 at 12:58

Juan

5,6112 gold badges17 silver badges27 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2019-01-25 12:53:52Z

2

In mysql a string column can be empty = '' or NUL (not values assigned )

so you should use for not empty

  SELECT * FORM my_table where some_column <> '';

or

  SELECT * FORM my_table where some_column  != '';

or not equal to a value

  SELECT * FORM my_table where some_column <> 'my_value';
  SELECT * FORM my_table where some_column != 'my_value';

for not null you must use the special operator IS NOT NULL

SELECT * FORM my_table where some_column IS NOT NULL;

edited Jan 25, 2019 at 12:53

answered Jan 25, 2019 at 12:48

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Rajat Verma Over a year ago

so when to use <> and != operators ?

ScaisEdge Over a year ago

You need <> or != when you want check if the string is different from a string value see .. updated ..answer

Collectives™ on Stack Overflow

Difference Between MySql <> NULL and IS NOT NULL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related