NULL is a unique value. It represents a value that is not defined and that is not really equal to BLANK (”).
While using it in queries, we need to use the utmost care.
Here are a few examples in which you might be confused with the output unless you are very comfortable using NULL.
1. When NULL is added to a number, the output is still NULL
Query: SELECT 1 + NULL;
Output:
+-----------+| 1 + NULL |+-----------+| (NULL) |+-----------+
2. When used with a string function such as CONCAT
Query: SELECT CONCAT(‘Indigo’,NULL);
Output:
+------------------------+| CONCAT('Indigo',NULL) |+------------------------+| (NULL) |+------------------------+
Alternatively, when the query is SELECT CONCAT(‘Indigo’,’ Nation’);
Output:
+-----------------------------+| CONCAT('Indigo',' Nation') |+-----------------------------+| Indigo Nation |+-----------------------------+