MySQL COALESCE() function
COALESCE() function
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
Code:
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-1: 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:

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).
Code:
SELECT pub_NAME,COALESCE(estd,country,pub_city)
FROM newpublisher;
Sample table: newpublisher
Sample Output:
mysql> SELECT pub_NAME,COALESCE(estd,country,pub_city) -> FROM newpublisher; +------------------------------+---------------------------------+ | pub_NAME | COALESCE(estd,country,pub_city) | +------------------------------+---------------------------------+ | Jex Max Publication | 1969-12-25 | | BPP Publication | 1985-10-01 | | New Harrold Publication | Australia | | Ultra Press Inc. | 1948-07-10 | | Mountain Publication | USA | | Summer Night Publication | 1990-12-10 | | Pieterson Grp. of Publishers | UK | | Novel Publisher Ltd. | 2000-01-01 | +------------------------------+---------------------------------+ 8 rows in set (0.04 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-coalesce-function- php mysql examples | w3resource</title>
<meta name="description" content="example-coalesce-function- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of the publishers with either their date of establishment or country or city:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers</th><th>date of establishment or country or city</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_NAME,COALESCE(estd,country,pub_city)
FROM newpublisher') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_NAME'] . "</td>";
echo "<td>" . $row['COALESCE(estd,country,pub_city)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-coalesce-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT pub_NAME,COALESCE(estd,country,pub_city) FROM newpublisher";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher</td>
<td>date of establishment or country or city</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_NAME")%></TD>
<TD><%=rs.getString("COALESCE(estd,country,pub_city)")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>
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

Previous: MySQL Comparison functions and operator BETWEEN AND
Next: NULL Safe equal to operator (<=>)
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook