mysql

Khóa học miễn phí MySQL – Coalesce() Function nhận dự án làm có lương

Posted on by user

MySQL – COALESCE() Function

Table of content

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''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user