NULL vs empty field in mysql?

[python72]

I see some of the fields in mysql show NULL and some are just empty, wonder what is the difference, is empty field still NULL?

[DavidAM]

In database theory, NULL means that the value is not known; while an empty string means the value is known to be nothing.  For instance: if you have a field in the users table for FavoriteColor; a NULL value means you don't know what their favorite color is, while an empty string would mean you know that they don't have a favorite color.

 

In practice, a NULL is treated differently than an empty string. This will never return any results because NULL is never equal to anything, not even another NULL,

SELECT * FROM Users WHERE FavoriteColor = NULL;

 

This will return records with an empty string but NOT records that are NULL

SELECT * FROM Users WHERE FavoriteColor = '';

 

This will return records that are NULL, but NOT empty strings

SELECT * FROM Users WHERE FavoriteColor IS NULL;

 

To get empty strings AND NULLs, we would use:

SELECT * FROM Users WHERE IFNULL(FavoriteColor, '') = '';

which says if the field is NULL pretend that it is an empty string.