Detailed explanation of COALESCE function example in MySQL

COALESCEis a SQL function that returns to the parameter listThe first non-NULL value. It is often used to deal with possible existenceNULLA scene with a value and provides a default or alternate value.

grammar

COALESCE(expression1, expression2, ..., expressionN)

• expression1, expression2, ..., expressionN: A set of expressions evaluated in order from left to right.
• The function returns the first non-NULL value if all expressions areNULL, then returnNULL。

Common uses

• Replace NULL value: Replace the default value with possibleNULLfield value.
• Multiple column priority: Select the highest priority and non-select from multiple columnsNULLvalue.
• Simplify nesting logic: Replace complexCASEExpressions.

Example

1. Replace NULL value

In queryNULLReplace with the specified default value.

SELECT COALESCE(NULL, 'Default Value') AS result;

result：

2. Used for field default values

Suppose there is a tableEmployees, including the salary of employees (salary) List. If the salary value isNULL, the default display is0。

SELECT employee_id, COALESCE(salary, 0) AS salary_with_default
FROM Employees;

Sample data：

result：

3. Multiple column priority

Select the first non-NULL value from multiple columns, for example, preferentially display emails in contact information, followed by phone numbers.

SELECT COALESCE(email, phone, 'No Contact') AS contact_info
FROM Customers;

Sample data：

result：

4. Combining the aggregation function

During the calculation process, the processing may beNULLvalue. For example, calculate the average value in a table, butNULLThe value uses the default value of 0.

SELECT AVG(COALESCE(score, 0)) AS avg_score
FROM Tests;

Things to note

• Data type consistency：
  • All parameters must be of the same or compatible data type.
  • If the parameter data types are inconsistent, the database will attempt to convert implicitly.
• performance：
  • COALESCEPerformance may be slightly affected when the parameter list is long.
• andIFNULLComparison:
  • Provided by MySQLIFNULL(expression, value)Functional similar toCOALESCE, but only two parameters are supported.

Summarize

COALESCEIt is a powerful and commonly used SQL function, mainly used to handleNULLValues ​​and implement flexible value selection strategies can make query logic clearer and more concise.

This is all about this article about COALESCE function in MySQL. For more related content of mysql coalesce function, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!