MySQL COALESCE() Function

MySQL COALESCE() Function

The MySQL COALESCE() function returns the first non-NULL value in a list of expressions. This function is essential for handling NULL values in SQL queries.

Syntax

SELECT COALESCE(expr1, expr2, ..., exprN) AS result
FROM table_name;

The COALESCE function has the following components:

  • expr1, expr2, ..., exprN: A list of expressions to be evaluated.
  • result: An alias for the resulting value.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL COALESCE Function

Let's look at some examples of the MySQL COALESCE function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    bonus DECIMAL(10, 2) DEFAULT NULL
);

This query creates a table named employees with columns for id, first_name, last_name, department, salary, and bonus.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, salary, bonus)
VALUES ('John', 'Doe', 'HR', 50000.00, NULL),
       ('Jane', 'Smith', 'IT', 60000.00, 5000.00),
       ('Jim', 'Brown', 'IT', 55000.00, NULL),
       ('Jake', 'White', 'HR', 52000.00, 3000.00),
       ('Jill', 'Green', 'Marketing', 45000.00, 2000.00);

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using COALESCE with WHERE Clause

Use the COALESCE function to handle NULL values in a query:

SELECT first_name, last_name, COALESCE(bonus, 0) AS bonus
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the bonus column, replacing NULL values with 0.

MySQL COALESCE WITH WHERE CLAUSE

Step 5: Using COALESCE with Multiple Columns

Use the COALESCE function with multiple columns:

SELECT first_name, last_name, COALESCE(bonus, salary * 0.1) AS bonus_or_default
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the bonus column, replacing NULL values with 10% of the salary.

MySQL COALESCE WITH MULTIPLE COLUMNS

Step 6: Using COALESCE with Multiple Expressions

Use the COALESCE function with multiple expressions:

SELECT first_name, last_name, COALESCE(bonus, salary * 0.1, 1000) AS bonus_or_default
FROM employees;

This query retrieves the first_name and last_name columns from the employees table and returns the bonus column, replacing NULL values with 10% of the salary or 1000 if both are NULL.

MySQL COALESCE WITH MULTIPLE EXPRESSIONS

Conclusion

The MySQL COALESCE function is a powerful tool for handling NULL values in SQL queries. Understanding how to use the COALESCE function is essential for effective data querying and analysis in MySQL.