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 |
+-----------------------------+