Mysql 简明教程
MySQL - COALESCE() Function
有时候，表中的一条记录可能缺少用户无法填入 0 的数据。在这样的情况下，MySQL 允许用户用 NULL 值来填充该记录。
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.
NULL 值只是数据库表中用于表示缺少的值或没有可插入数据的占位符。
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
MySQL COALESCE() 函数返回表达式列表中的第一个非 NULL 值。它将多个表达式作为参数，并返回第一个非 NULL 表达式的值。如果所有表达式都是 NULL，它将返回 NULL。
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.
当 COALESCE() 函数用于 MySQL 表，其中参数表示需要比较的字段名称时，函数会比较这些列中相应的值，并检索第一个 NOT NULL 值。
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
以下是 COALESCE() 函数的基本语法：
Following is the basic syntax for the COALESCE() function −
SELECT COALESCE (expression_1, expression_2, ..., expression_n)
FROM table_name;Example
在以下查询中，我们正在从传递给 COALESCE() 函数的参数列表中检索第一个非 NULL 值：
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;Example
现在，让我们创建一个名为 “CUSTOMERS” 的表，以使用以下查询来存储客户的个人详细信息，包括姓名、年龄、地址和薪水：
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)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
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 );以下是获得的 CUSTOMERS 表：
Following is the CUSTOMERS table obtained −
现在，让我们使用 SELECT 语句检索一个包含 "NAME"、"AGE" 和 "SALARY" 列中值的结果集。我们还将 "AGE" 和 "SALARY" 作为参数传递给 COALESCE() 函数，并且返回值将显示在另一个名为 "RESULT" 的列中：
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" 列包含 "AGE" 和 "SALARY" 列中的第一个非 NULL 值。例如，在第一条记录中，"SALARY" 列有个 NULL 值，但是 "AGE" 包含非 NULL 值，因此 "RESULT" 列显示年龄值。
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.
在两列都包含非 NULL 值的情况下，COALESCE() 函数返回最高值。
In cases where both columns contain non-NULL values, the COALESCE() function returns the highest value.