MySQL COALESCE and NULLIF Function

Last updated on October 30th, 2018 at 04:25 am

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)

Leave a Reply