Difference between MySQL IS NOT NULL and != ''

Question

Is there any difference between MySQL

IF (myText IS NOT NULL) THEN

and

IF (myText != '') THEN

Answer 1

Yes there is a big difference between a NULL value and a blank/empty value.

Here's one resource that describes the differences.

When myText IS NULL:

• myText IS NOT NULL evaluates to FALSE
• myText != '' evaluates to NULL (which essentially behaves the same as FALSE would in this specific case you wrote)

However, you should not get into the habit of treating them the same, since most of the time they will behave differently: For example:

Assume you have a table tbl:

id   text
1    NULL
2    
3    abc

Note: 1 contains a NULL value, and 2 contains an empty string ('').

If you run the following query:

SELECT * FROM tbl WHERE text != ''

... it will return record 3.

If you run the following query:

SELECT * FROM tbl WHERE text IS NOT NULL

... it will return records 2 and 3.

Answer 2

Yes there is a difference.

In simple words, myText IS NOT NULL specifies that myText is having some value which could be '' too.

Where as myText != '' specifies that it returns TRUE, if myText does NOT contain an empty string.

Answer 3

There is a difference. If the default value of a column is "NULL", then if no data has been set for a field, it is truly null. However, if the value of a field has been updated as '', it is not NULL, rather it is empty.

See here for more information Link