Mysql 简明教程
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 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;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)
);现在，使用如下所示的 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 );Following is the CUSTOMERS table obtained −
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
获得的结果如下所示 −
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.