w3resource logo


MySQL coalesce() function

MySQL COALESCE() function

rating Average rating 8 out of 10. Total 39 users rated.

<<PreviousNext>>

Description

MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values.

MySQL Version : 5.6

Syntax

COALESCE(value1,value2,value3,...)

The above syntax is equivalent to the following IF-THEN-ELSE statement

  IF value1 is not NULL THEN
     result = value1;
  ELSIF value2 is not NULL THEN
     result = value2;
  ELSIF value3 is not NULL THEN
     result = value3;
  ELSE
     result = NULL;
  END IF;              

Example : MySQL COALESCE() function

mysql> SELECT COALESCE(NULL, 2, 3);
+----------------------+
| COALESCE(NULL, 2, 3) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.02 sec)
mysql> SELECT COALESCE(NULL, NULL, NULL);
+----------------------------+
| COALESCE(NULL, NULL, NULL) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

Pictorial Presentation

MySQL COALESCE function

Example -2 :

The following MySQL statement returns date of establishment for the Jex Max Publication, BPP Publication, Ultra Press Inc., Night Publication and Novel Publisher Ltd. For New Harrold Publication,Mountain Publication Summer, Pieterson Grp. of Publishers, the query returns the country, since they don't have any date of establishment (NULL).

SELECT pub_NAME,COALESCE(estd,country,pub_city)
FROM newpublisher;

Sample table : newpublisher

Output

MySQL COALESCE()

PHP script

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example-coalesce-function- php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<h2>A list of the publishers with either their date of establishment or country or city : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='250' align='center'>Publishers</td><td width='200' align='center'>date of establishment or country or city</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT pub_NAME,COALESCE(estd,country,pub_city)
FROM newpublisher");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_NAME'] . "</td>";
echo "<td align='center' width='200'>" . $row['COALESCE(estd,country,pub_city)'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>

View the example in browser

Difference between IFNULL() and COALESCE() function in MySQL

In MySQL, IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression otherwise it returns the second expression whereas COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. See the following examples :

mysql> SELECT IFNULL('Red', 'Green');
+------------------------+
| IFNULL('Red', 'Green') |
+------------------------+
| Red                    |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT IFNULL(NULL, 'Green');
+-----------------------+
| IFNULL(NULL, 'Green') |
+-----------------------+
| Green                 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, 'Green');
+-------------------------+
| COALESCE(NULL, 'Green') |
+-------------------------+
| Green                   |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, 'Red', 'Green');
+--------------------------------+
| COALESCE(NULL, 'Red', 'Green') |
+--------------------------------+
| Red                            |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, NULL, NULL, 'Red');
+-----------------------------------+
| COALESCE(NULL, NULL, NULL, 'Red') |
+-----------------------------------+
| Red                               |
+-----------------------------------+
1 row in set (0.00 sec)

See also : NOT IN(), IS NULL(), LEAST()

Slideshow of MySQL Comparison Function and Operators

MySQL Comparison Function and Operators, slide presentation



We have compiled a number of MySQL Questions and Answers. Check and assess you MySQL skill by visiting those questions and answers.

<<PreviousNext>>