We will be discussing MySQL COALESCE and NULLIF Function in this post. We will also be showing MySQL COALESCE examples and NULLIF MySQL Function example
MySQL COALESCE Function
COALESCE 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 the 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
Below are some examples of COALESCE Function
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 that contains employee mobile_number,office_number, and home_number. It may contain a null value for any of these fields. Now you want to select the phone in priority mobile_number > office_number > home_number in case of null values. So we can use the below coalesce statement
select emp_name, coalesce(mobile_number,office_number,home_number) contact_no from emp;
MySQL NULLIF function
Nullif Function in MySQL is a very useful function to compare two values and give null in case they are the same, give val1 in case they are not the same
NULLIF(val1,val2)
Usage
Below are some examples of the NULLIF statement.
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)
I hope you find this post on MySQL Database COALESCE and NULLIF Function interesting and useful. Please do provide the feedback to improve
Related Articles
Step-by-Step Guide for Installing MySQL on Windows
Auto Increment Column – Sequence as Default Value in Oracle and MySQL
Top 51 Frequently Asked MySQL Interview Questions And Answers
Step by step guide to build local Apache PHP MySQL development environment on windows
How to reset MySQL root password
