MySQL - COALESCE() Function

Sometimes a record in a table might have missing data that the user cannot fill with zeroes. In such cases, MySQL allows the user to fill that record with a NULL value.

A NULL value is nothing but a placeholder in database tables to represent missing values or when the data is not available to insert.

The MySQL COALESCE() Function

The MySQL COALESCE() function returns the first non-NULL value in a list of expressions. It takes multiple expressions as arguments and returns the value of the first expression that is not NULL. If all expressions are NULL, it returns NULL.

When all its arguments are NOT NULL, the COALESCE() function evaluates the values based on the priority of their datatypes. For example, an integer is always prioritized over a character expression in the COALESCE() function, resulting in an integer as the output.

When the COALESCE() function is used on MySQL tables with arguments representing field names that require comparison, the function compares the corresponding values in these columns, and retrieves the first occurrence that is NOT NULL.

Syntax

Following is the basic syntax for the COALESCE() function −

SELECT COALESCE (expression_1, expression_2, ..., expression_n) 
FROM table_name;

Example

In the following query, we are retrieving the first occurrence of nnon-NULL value from the list of arguments passed to the COALESCE() function −

SELECT COALESCE(NULL, NULL, 'Hello', 'Tutorialspoint') 
AS RESULT;

Output

Following is the output obtained −

Example

Now, let us create a table named "CUSTOMERS" to store personal details of customers, including their name, age, address, and salary using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20),
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, NULL, NULL ),
(2, 'Khilan', 25, 'Delhi', NULL ),
(3, 'kaushik', 23, 'Kota', NULL ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', NULL ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Following is the CUSTOMERS table obtained −

Now, let us use the SELECT statement to retrieve a result-set that contains values from the "NAME," "AGE," and "SALARY" columns. We will also pass "AGE" and "SALARY" as arguments to the COALESCE() function, and the return values will be displayed in another column named "RESULT." −

SELECT NAME, AGE, SALARY, 
COALESCE(SALARY, AGE) RESULT 
FROM CUSTOMERS;

Output

The result obtained is as shown below −

In the result-set, you will notice that the "NAME," "AGE," and "SALARY" values are displayed normally. However, the "RESULT" column contains the first non-NULL value from the "AGE" and "SALARY" columns. For example, in the first record, the "SALARY" column has a NULL value, but "AGE" holds a non-NULL value, so the "RESULT" column displays the age value.

In cases where both columns contain non-NULL values, the COALESCE() function returns the highest value.

Coalesce() Function Using Client Program

We can also perform coalesce() function using client program.

Syntax

$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
$mysqli->query($sql);

sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
con.query(sql)

String sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
statement.executeQuery(sql);

coalesce_query = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
cursorObj.execute(coalesce_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// Create table Customer   
$sql = 'CREATE TABLE IF NOT EXISTS CUSTOMER ( ID INT NOT NULL, NAME VARCHAR (20), AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID) )';
$result = $mysqli->query($sql);
if ($result) {
    echo "Table created successfully...!\n";
}

// Insert data into the created table    
$q = "INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES
    (1, 'Ramesh', 32, NULL, NULL ),
    (2, 'Khilan', 25, 'Delhi', NULL ),
    (3, 'kaushik', 23, 'Kota', NULL ),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
    (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
    (6, 'Komal', 22, 'MP', NULL ),
    (7, 'Muffy', 24, 'Indore', 10000.00 )";
if ($res = $mysqli->query($q)) {
    echo "Data inserted successfully...! \n";
}

$sql = "SELECT * FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "\nTable's records before coalesce()...!\n\n";
    while ($row = $res->fetch_array()) {
        printf(
            "iD: %d, Name: %s, Age: %d, Address: %s, Salary: %2f",
            $row['ID'], $row['NAME'], $row['AGE'], $row['ADDRESS'], $row['SALARY']
        );
        printf("\n");
    }
}
//using coalesce function:
$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "\nTable's records after coalesce()...!\n\n";
    while ($row = $res->fetch_array()) {
        printf(
            "Name: %s, Age: %d, Salary: %2f, Result: %2f",
            $row['NAME'], $row['AGE'], $row['SALARY'], $row['RESULT']
        );
        printf("\n");
    }
}
$mysqli->close();

Table's records before coalesce()...!
iD: 1, Name: Ramesh, Age: 32, Address: , Salary: 0.000000
iD: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 0.000000
iD: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 0.000000
iD: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.000000
iD: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.000000
iD: 6, Name: Komal, Age: 22, Address: MP, Salary: 0.000000
iD: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.000000

Table's records after coalesce()...!
Name: Ramesh, Age: 32, Salary: 0.000000, Result: 32.000000
Name: Khilan, Age: 25, Salary: 0.000000, Result: 25.000000
Name: kaushik, Age: 23, Salary: 0.000000, Result: 23.000000
Name: Chaitali, Age: 25, Salary: 6500.000000, Result: 6500.000000
Name: Hardik, Age: 27, Salary: 8500.000000, Result: 8500.000000
Name: Komal, Age: 22, Salary: 0.000000, Result: 22.000000
Name: Muffy, Age: 24, Salary: 10000.000000, Result: 10000.000000     

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "CREATE TABLE CUSTOMER (ID INT NOT NULL,NAME VARCHAR (20),AGE INT,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY(ID));"
    con.query(sql);

    sql = "INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, NULL, NULL ),(2, 'Khilan', 25, 'Delhi', NULL ),(3, 'kaushik', 23, 'Kota', NULL ),(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik', 27, 'Bhopal', 8500.00 ),(6, 'Komal', 22, 'MP', NULL ),(7, 'Muffy', 24, 'Indore', 10000.00 );"
    con.query(sql);

    sql = "SELECT * FROM CUSTOMER;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Records of CUSTOMER Table:**");
      console.log(result);
      console.log("--------------------------");
    });

    //Usage of COALESCE function
    sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});       

 
Connected!
--------------------------
**Records of CUSTOMER Table:**
[
  { ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: null, SALARY: null },
  { ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: null },
  { ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: null },
  { ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'},
  { ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'},
  { ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: null },
  { ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'}
]
--------------------------
[
  { NAME: 'Ramesh', AGE: 32, SALARY: null, RESULT: '32.00' },
  { NAME: 'Khilan', AGE: 25, SALARY: null, RESULT: '25.00' },
  { NAME: 'kaushik', AGE: 23, SALARY: null, RESULT: '23.00' },
  { NAME: 'Chaitali', AGE: 25, SALARY: '6500.00', RESULT: '6500.00' },
  { NAME: 'Hardik', AGE: 27, SALARY: '8500.00', RESULT: '8500.00' },
  { NAME: 'Komal', AGE: 22, SALARY: null, RESULT: '22.00' },
  { NAME: 'Muffy', AGE: 24, SALARY: '10000.00', RESULT: '10000.00' }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Coalesce {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         System.out.println("Table records...!\n");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM CUSTOMERS");
         while (resultSet.next()){
            System.out.println("Id: "+resultSet.getString(1)+" Name: "+resultSet.getString(2)+" Age: "+resultSet.getString(3)+" Address: "+resultSet.getString(4)+" Salary: "+resultSet.getString(4));
         }

         //using coalesce function:
         System.out.println("\nAfter using Coalesce function...!\n");
         String sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMERS";
         ResultSet resultSet1 = statement.executeQuery(sql);
         while (resultSet1.next()){
            System.out.println("Name: "+resultSet1.getString(1)+" Age: "+resultSet1.getString(2)+" Salary: "+resultSet1.getString(3)+" Result: "+resultSet1.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}       

Connected successfully...!
Table records...!

Id: 1 Name: Ramesh Age: 32 Address: Ahmedabad Salary: Ahmedabad
Id: 2 Name: Khilan Age: 25 Address: Delhi Salary: Delhi
Id: 3 Name: kaushik Age: 23 Address: Kota Salary: Kota
Id: 4 Name: Chaitali Age: 25 Address: Mumbai Salary: Mumbai
Id: 5 Name: Hardik Age: 27 Address: Bhopal Salary: Bhopal
Id: 6 Name: Komal Age: 22 Address: MP Salary: MP
Id: 7 Name: Muffy Age: 24 Address: Indore Salary: Indore

After using Coalesce function...!

Name: Ramesh Age: 32 Salary: 2000.00 Result: 2000.00
Name: Khilan Age: 25 Salary: 1500.00 Result: 1500.00
Name: kaushik Age: 23 Salary: 2000.00 Result: 2000.00
Name: Chaitali Age: 25 Salary: 6500.00 Result: 6500.00
Name: Hardik Age: 27 Salary: 8500.00 Result: 8500.00
Name: Komal Age: 22 Salary: 4500.00 Result: 4500.00
Name: Muffy Age: 24 Salary: 10000.00 Result: 10000.00        

import mysql.connector
# Establishing the connection 
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
# Creating the 'CUSTOMER' table
create_table_query = '''
CREATE TABLE CUSTOMER (
    ID INT NOT NULL,
    NAME VARCHAR(20),
    AGE INT,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);
'''
cursorObj.execute(create_table_query)
print("Table 'CUSTOMER' is created successfully!")
# Inserting records into the 'CUSTOMER' table
insert_query = "INSERT INTO CUSTOMER (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
values = [
    (1, 'Ramesh', 32, None, None),
    (2, 'Khilan', 25, 'Delhi', None),
    (3, 'Kaushik', 23, 'Kota', None),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'MP', None),
    (7, 'Muffy', 24, 'Indore', 10000.00)
]
cursorObj.executemany(insert_query, values)
print("Values inserted successfully!")
# Displaying the contents of the 'CUSTOMER' table
display_table_query = "SELECT * FROM CUSTOMER;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("\nCUSTOMER Table:")
for result in results:
    print(result)
# Coalesce expression
coalesce_query = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER;"
cursorObj.execute(coalesce_query)
results = cursorObj.fetchall()
print("\nCoalesce Table:")
for result in results:
    print(result)
# Closing the cursor and connection
cursorObj.close()
connection.close()       

Table 'CUSTOMER' is created successfully!
Values inserted successfully!

CUSTOMER Table:
(1, 'Ramesh', 32, None, None)
(2, 'Khilan', 25, 'Delhi', None)
(3, 'Kaushik', 23, 'Kota', None)
(4, 'Chaitali', 25, 'Mumbai', Decimal('6500.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))
(6, 'Komal', 22, 'MP', None)
(7, 'Muffy', 24, 'Indore', Decimal('10000.00'))

Coalesce Table:
('Ramesh', 32, None, Decimal('32.00'))
('Khilan', 25, None, Decimal('25.00'))
('Kaushik', 23, None, Decimal('23.00'))
('Chaitali', 25, Decimal('6500.00'), Decimal('6500.00'))
('Hardik', 27, Decimal('8500.00'), Decimal('8500.00'))
('Komal', 22, None, Decimal('22.00'))
('Muffy', 24, Decimal('10000.00'), Decimal('10000.00'))