How to Handle Null Values in MYSQL Server

The concept of the null value is a common source of problems for an outsider; null is the same thing as an empty string in MySQL server does nothing to disallow null as the value of a distribute expression, whether it is a column value or the value of a user supplied expression. Null is unusual because it doesn't represent a specific value the way that numeric, string, or temporal values do. Even though it is permitted to use Null as the value of an expression that must otherwise yield an integer, it is important to keep in mind that null is not a number.

We suppose that a table whose name is tcount_tbl in 'TUTORIALS' database and contains two columns tutorial_author and tutorial_count, where a NULL tutorial_count indicates that the value is unknown.

Example:

mysql> create table tcount_tbl (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> );

Insert the values into the tcount_tbl table

mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('mahran', 20);
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('mahnaz', NULL);
mysql> INSERT INTO tcount_tbl
->(tutorial_author, tutorial_count) values ('Jen', NULL);
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('Gill', 20);




Here we can see that = and != do not work with NULL values as follows

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

To find records where the tutorial_count column is or is not NULL, the queries should be written like this.

mysql> SELECT * FROM tcount_tbl
    -> WHERE tutorial_count IS NULL;



mysql> SELECT * from tcount_tbl
    -> WHERE tutorial_count IS NOT NULL;



Null Values related functions in MySQL

In MYSQL there are some functions intended specifically for use with NULL values which include ISNULL() and IFNULL().  ISNULL() is true if its argument is NULL and false otherwise.

mysql > SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument.

mysql > SELECT IFNULL(NULL,'a'), IFNULL(0,'b');

Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value.

For example : Any NULL argument passed to CONCAT() causes it to return NULL:

mysql > SELECT CONCAT('a','b'), CONCAT('a',NULL,'b');

But not all functions behave that way. CONCAT_WS() (concatenate with separator) simply ignores NULL arguments entirely:

SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b');

Resources 

Here are some useful related resources:

• Handling Null Values in DataGrid
  
• Memory handling on NULL values using sparse columns
  
• Checking Nullable Values in .NET
  
• C# 2.0 Feature: Nullable Types
  
• Inserting a Null Value for Integer columns in Windows Azure Table