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://127.0.0.1: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'))
打印页面
上一个
下一个
广告