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).

 INSERT into Singer
        (F_Name, L_Name, Birth_place, Language) 
        values 
        ("", "Homer", NULL, "Greek"),
        ("", "Sting", NULL, "English"),
        ("Jonny", "Five", NULL, "Binary");

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

 
 SELECT * from Singer WHERE Birth_place IS NULL;
 or
 SELECT * from Singer WHERE Birth_place IS NOT NULL;
 or
 SELECT * from Singer WHERE isNull(Birth_place)

Remember, COUNT never counts NULLS.

 select count(Birth_place) from Singer;
 0
 and sum(NULL) gives a NULL answer.

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

 SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE 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:

 SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

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

 ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

 EXP(SUM(LOG(COALESCE(''*the field you want to multiply*'',1)))

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.

 SELECT t4.gene_name, COALESCE(g2d.score,0), 
 COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
 FROM t4 
 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
 LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
 LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

Use of IFNULL() in your SELECT statement is to make the NULL any value you wish.

 IFNULL(expr1,expr2)

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:

 mysql> SELECT IFNULL(1,0);
 -> 1
 mysql> SELECT IFNULL(NULL,10);
 -> 10
 mysql> SELECT IFNULL(1/0,10);
 -> 10
 mysql> SELECT IFNULL(1/0,'yes');
 -> 'yes'

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.

 DELETE FROM my_table WHERE field > NULL (or function returning NULL)

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

 SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]

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

SELECT *
FROM  `information_schema`.`COLUMNS`
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'my_table'