MySQL - If you think you know what is NULL? Think again!Jan 12, 2011

The concept of the NULL value is a common source of confusion among most of the developers. The NULL value can be surprising until you get used to it. Most of the developers think that NULL is the same thing as an empty string "" or 0 and they think that it is not possible to insert a 0 or an empty string into a column defined as NOT NULL. But this is not the case 0 and empty string are values, whereas NULL means "not having a value" or "a missing unknown value". Therefore it is possible to insert a 0 or empty string into a column defined as NOT NULL, as these are in fact NOT NULL.

In MySQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value. For example all columns in the following example will return NULL:

SELECT CONCAT(NULL, '1'), NULL = 0, NULL = '', NULL > 1, NULL < 1, 
NULL + 1, NULL * 1, NULL - 1, NULL <> 1, NULL = NULL;

To search for column values that are NULL, you cannot use an expression like column_name=NULL because it will never be true for any expression. To look for NULL values, you must use column_name IS NULL or column_name<=>NULL (where <=> is the equality comparison operator).

If you are using InnoDB, MyISAM or MEMORY storage engine you can add an index on a column that can have NULL values. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL in indexed column.

When using ORDER BY, GROUP BY, or DISTINCT all NULL values are regarded as equal. For ORDER BY, NULL values are presented first for ASC order.

MySQL handles NULL values differently for some data types. If you insert NULL into a TIMESTAMP column, the current date and time is inserted. If you insert NULL into an integer or floating point column that has the AUTO_INCREMENT attribute, the next number in the sequence is inserted.

Aggregate functions such as MIN(), MAX(), SUM(), and COUNT() ignore NULL values.

Count(*) vs Count(column_name)

It is a common assumption that Count(*) and Count(column_name) will produce the same result if WHERE clause is same but this is not the case. There is a slight difference between the working of these two. If you use Count(*) it will return the total number of rows but if you use Count(column_name) and column_name have NULL values then they will not be counted and you will get total number of NON NULL values. Hence Count(*) will be faster as well because it will not require reading the rows to check if value is NULL.

blog comments powered by Disqus
Me Hi! My name is Zeeshan Muhammad Khan (nick name Shan) and I am a software engineer, database developer, web developer, programming geek, statistics geek, mathematics geek, system analyst and maintainer of this site. read more

Web Shelf