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 −
RESULT |
---|
Hello |
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 −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
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 |
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 −
NAME | AGE | SALARY | RESULT |
---|---|---|---|
Ramesh | 32 | NULL | 32.00 |
Khilan | 25 | NULL | 25.00 |
Kaushik | 23 | NULL | 23.00 |
Chaitali | 25 | 6500.00 | 6500.00 |
Hardik | 27 | 8500.00 | 8500.00 |
Komal | 22 | NULL | 22.00 |
Muffy | 24 | 10000.00 | 10000.00 |
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
To perform COALESCE() function through a PHP program, we need to execute the “SELECT” statement using the mysqli function query() as follows −
$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER"; $mysqli->query($sql);
To perform COALESCE() function through a JavaScript program, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows −
sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER"; con.query(sql)
To perform COALESCE() function through a Java program, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows −
String sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER"; statement.executeQuery(sql);
To perform COALESCE() function through a Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows −
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()...!nn"; 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()...!nn"; 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();
Output
The output obtained is as shown below −
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); }); });
Output
The output obtained is as shown below −
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(); } } }
Output
The output obtained is as shown below −
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()
Output
The output obtained is as shown below −
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''))