MYSQL NULL And IS NOT NULL

MYSQL NULL And IS NOT NULL

Using ‘Null’ for a database column(field) will give any row a value of ‘NULL’. That part should be obvious. But, why not just leave it blank? Why should I use ‘NULL’ and not juts use an empty string? Well, there are a few differences. First of all, Null means “a missing unknown value” while an empty string is actually a value. Therefore, when you have 0 or an empty string in a column it is NOT NULL. Only a value of ‘NULL’ is null. If you use an empty string, you can always write a clause like WHERE tablename1.columnname = tablename2.columnname. But, if one of the columns is NULL you cannot join on a NULL value. You would need the query to read WHERE tablename1.columnname IS NULL AND tablename2.columnname IS NULL.

With MySQL, a NULL value cannot be compared to anything else. To see if a column is ‘NULL’ you use ‘IS NULL’ in a query, while ‘IS NOT NULL’ can be used for NOT NULL values. The query cannot use a clause like columnname=NULL. Alternatively, columnname<=>NULL can be used to find ‘NULL’ values.

You can add indexes on InnoDB or MyISAM tables that contain NULL values. Since indexes enhance performance, this a good feature. When you use a clause like ORDER BY or GROUP BY, all; NULL values are equal; just like ordering by lastname asc where three people have the same name ‘Anderson’. When NULL values exist in a column, they will show up first if the data is output by ascending order.

August 28, 2012 by kent