MySQL/Language/Using NULL

Description
Null is a special logical value in SQL. Most programming languages have 2 values of logic: True and False. SQL also has NULL which means "Unknown". A NULL value can be set.

NULL is a non-value, so it can be assigned to TEXT columns, INTEGER columns or any other datatype. A column can not contain NULLs only if it has been declared as NOT NULL (see ALTER TABLE).

Do not quote the NULL. If you quote a Null then you name the person NULL. For some strange reason, NULLs do not show visually on windows XP in Varchar fields but they do in Fedora's version, so versions of mysql can give different outputs. Here we set the value of Sting and Homer's first name to a zero length string "", because we KNOW they have NO first name, but we KNOW we do not know the place they were born. To check for a NULLs use

Remember, COUNT never counts NULLS.

Normal operations (comparisons, expressions...) return NULL if at least one of the compared items is NULL:

because all the expressions between in parenthesis return NULL. It's definitely logical: if you don't know the value represented by NULL, you don't know is it's =1 or <>1. Be aware that even (NULL=NULL) and (NOT NULL) return NULL.

Dealing with NULL
The function 'COALESCE' can simplify working with null values. for example, to avoid showing null values by treating null as zero, you can type:

In a date field, to treat NULL as the current date:

The coalesce function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances.

Use of IFNULL in your SELECT statement is to make the NULL any value you wish. If expr1 is not NULL, IFNULL returns expr1, else it returns expr2.

IFNULL returns a numeric or string value, depending on the context in which it is used: Null handling can be very counter intuitive and could cause problems if you have an incorrect function in a delete statement that returns null. For example the following query will delete all entries.

If you want to have NULL values presented last when doing an ORDER BY, try this:

Finally, to determine the table fields which can't be null:

MySQL/NULL