MySQL - COALESCE() 函式

Table of content
上一個
下一個

有時表中的記錄可能缺少使用者無法用零填充的資料。在這種情況下，MySQL 允許使用者用 NULL 值填充該記錄。

NULL 值只不過是資料庫表中的一個佔位符，用於表示缺少的值或資料不可用以插入。

MySQL COALESCE() 函式

MySQL 的 **COALESCE()** 函式返回表示式列表中的第一個非 NULL 值。它接受多個表示式作為引數，並返回第一個不為 NULL 的表示式的值。如果所有表示式都為 NULL，則返回 NULL。

當所有引數都不為 NULL 時，COALESCE() 函式會根據其資料型別的優先順序評估值。例如，在 COALESCE() 函式中，整數始終優先於字元表示式，從而導致整數作為輸出。

當 COALESCE() 函式用於具有表示需要比較的欄位名的引數的 MySQL 表時，該函式會比較這些列中的對應值，並檢索第一個不為 NULL 的值。

語法

以下是 **COALESCE()** 函式的基本語法：

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

示例

在以下查詢中，我們從傳遞給 COALESCE() 函式的引數列表中檢索第一個非 NULL 值：

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

輸出

以下是獲得的輸出：

結果
Hello

示例

現在，讓我們建立一個名為“CUSTOMERS”的表來儲存客戶的個人詳細資訊，包括他們的姓名、年齡、地址和薪水，使用以下查詢：

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

現在，使用 INSERT 語句將值插入此表，如下所示：

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

以下是獲得的 CUSTOMERS 表：

ID 姓名 年齡 地址 薪水
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

現在，讓我們使用 SELECT 語句檢索一個結果集，其中包含“NAME”、“AGE”和“SALARY”列中的值。我們還將“AGE”和“SALARY”作為引數傳遞給 COALESCE() 函式，返回的值將顯示在名為“RESULT”的另一列中。

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

輸出

獲得的結果如下所示：

姓名 年齡 薪水 結果
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

在結果集中，您會注意到“NAME”、“AGE”和“SALARY”值正常顯示。但是，“RESULT”列包含“AGE”和“SALARY”列中的第一個非 NULL 值。例如，在第一條記錄中，“SALARY”列的值為 NULL，但“AGE”包含非 NULL 值，因此“RESULT”列顯示年齡值。

在兩列都包含非 NULL 值的情況下，COALESCE() 函式將返回最高值。

使用客戶端程式的 Coalesce() 函式

我們也可以使用客戶端程式執行 coalesce() 函式。

語法

PHP NodeJS Java Python

要透過 PHP 程式執行 COALESCE() 函式，我們需要使用 **mysqli** 函式 **query()** 執行“SELECT”語句，如下所示：

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

要透過 JavaScript 程式執行 COALESCE() 函式，我們需要使用 **mysql2** 庫的 **query()** 函式執行“SELECT”語句，如下所示：

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

要透過 Java 程式執行 COALESCE() 函式，我們需要使用 **JDBC** 函式 **executeQuery()** 執行“SELECT”語句，如下所示：

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

要透過 Python 程式執行 COALESCE() 函式，我們需要使用 **MySQL Connector/Python** 的 **execute()** 函式執行“SELECT”語句，如下所示：

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

示例

以下是程式：

PHP NodeJS Java Python
$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://: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'))
列印頁面
上一個
下一個
廣告