MySQL COALESCE and NULLIF Function

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