We will be discussing MySQL COALESCE and NULLIF Function in this post with lot of examples.
COALESCE Function
COALESCE Function in Mysql is a very useful function to select first not null values from the list of values
Syntax
COALESCE(val1, val2,val3,…valn)
The above syntax is equivalent to the following IF-THEN-ELSE statement
IF val1 is not NULL THEN val1
ELSIF val2 is not NULL THEN val2
ELSIF val3 is not NULL THEN val3
ELSE NULL;
END IF
This is also equivalent to case expression
Case
when val1 is not NULL then val1
when val2 is not NULL then val2
when val3 is not NULL then val3
else NULL
end
Usage
mysql> select COALESCE(1,2,3,null);
+———————-+
| COALESCE(1,2,3,null) |
+———————-+
| 1 |
+———————-+
1 row in set (0.00 sec)
mysql> select COALESCE(null,null,’a’,’b’,null);
+———————————-+
| COALESCE(null,null,’a’,’b’,null) |
+———————————-+
| a |
+———————————-+
1 row in set (0.00 sec)
mysql> select COALESCE(null,null,null);
+————————–+
| COALESCE(null,null,null) |
+————————–+
| NULL |
+————————–+
1 row in set (0.00 sec)
Suppose you have a table EMP which contains employee mobile_number,office_number and home_number. It may contains null value for any of these field. Now you want to select the phone in priority mobile_number > office_number > home_number incase of null values. So we can use below coalesce statement
select emp_name, coalesce(mobile_number,office_number,home_number) contact_no from emp;
NULLIF function
Nullif Function in Mysql is a very useful function to compare two values and give null incase they are same, give val1 in case the they are same same
NULLIF(val1,val2)
Usage
mysql> select nullif(1,2);
+————-+
| nullif(1,2) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)
mysql> select nullif(2,1);
+————-+
| nullif(2,1) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)
mysql> select nullif(1,1);
+————-+
| nullif(1,1) |
+————-+
| NULL |
+————-+
1 row in set (0.00 sec)