How to deal with NULL values in MySQL

Null values are different than any other value in the world, you cannot use the qual operator when working with null values, query like this will not work:

SELECT * FROM users WHERE username = null;

Luckily, MySQL have built-in command that works with null values, such as IS NULL, IS NOT NULL, and special operator <=>, here's the example:

SELECT * FROM users WHERE username IS NULL;

SELECT * FROM users WHERE username IS NOT NULL;

SELECT * FROM users WHERE username <=> NULL;

The command IS NULL and <=> NULL are the same, it will look for values that are null, on the other hand, the IS NOT NULL is the opposite, it will select values that are not null.

SELECT NULL = NULL, NULL <=> NULL;

MySQL also provide function related to null values, IF() and IFNULL(), basically with this function we could convert null values into something more meaningful, like string 'unknown' for example.

IF(expr1 IF NOT NULL, expr1, expr2)

IFNULL(expr1, expr2)

Example:

SELECT IF(username IS NULL, 'unknown', username) as name FROM users 

SELECT IFNULL(username, 'unknown') as name FROM users

NOTE: you can also use CASE WHEN statement if you like, instead of IF() and IFNULL() function.