SQL “Left Outer Join” can count NULL!

A friend of mine has recently consulted me on a common situation to calculate values “let’s say an employee salary” from different columns in different tables. The common way to do that is to use Left Outer Join, but the problem is that unmatched values will be returned as NULL. It makes sense that a NULL value will be returned in case of having any NULL value in the operation.

The scenario will be as the following:

mysql> select (1+2+3);
+———+
| (1+2+3) |
+———+
|       6 |
+———+
1 row in set (0.00 sec)

mysql> select (1+2+NULL);
+————+
| (1+2+NULL) |
+————+
|       NULL |
+————+
1 row in set (0.00 sec)

In MS SQL Server, this problem can be easily solved using ISNULL with replacement value. For instance,

ISNULL(salary, 0.00)

So, in case that the salary value was NULL, the returned value will be 0.00.

In mySQL, ISNULL is a little bit different than that. It simply tests if the provided value is NULL or not (yeah, 1 if it’s NULL and 0 if it did not match it).

However, another function can actually help in this case. IFNULL represents the same functionality provided by MSSQLSERVER’s ISNULL with replacement value.

mysql> select (ISNULL(NULL));
+—————-+
| (ISNULL(NULL)) |
+—————-+
|              1 |
+—————-+
1 row in set (0.00 sec)

mysql> select (ISNULL(10));
+————–+
| (ISNULL(10)) |
+————–+
|            0 |
+————–+
1 row in set (0.00 sec)

mysql> select (IFNULL(NULL,10));
+——————-+
| (IFNULL(NULL,10)) |
+——————-+
|                10 |
+——————-+
1 row in set (0.00 sec)

mysql> select (IFNULL(1,10));
+—————-+
| (IFNULL(1,10)) |
+—————-+
|              1 |
+—————-+
1 row in set (0.00 sec)

As a conclusion, these functions (ISNULL/IFNULL) can be very useful to enhance and optimize the quality of code that’s been written in many complex situations.