Summary: In this tutorial, you will learn how to use the MySQL COALESCE function to substitute NULL values.

Introduction to MySQL COALESCE function

The COALESCE function takes a number of arguments and returns the first non-NULL argument. In case all arguments are NULL, the COALESCEfunction returns NULL.

The following illustrates the COALESCE function syntax:

COALESCE(value1,value2,...);Code language: SQL (Structured Query Language) (sql)

For example:

SELECT COALESCE(NULL, 0);-- 0SELECT COALESCE(NULL, NULL);-- NULL;Code language: SQL (Structured Query Language) (sql)

MySQL COALESCE function examples

See the following customers table in the sample database:

The following query returns the customer name, city, state, and country of all customers in the customerstable.

SELECT
    customerName, city, state, country
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

As you see, the state column has NULL values because some of this information is not applicable to the country of some customers.

To substitute the NULL value in the result set, you can use the COALESCE function as follows:

SELECT
    customerName, city, COALESCE(state, 'N/A'), country
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

In this example, if the value in the state column is NULL, the COALESCE function will substitute it with the N/A string. Otherwise, it returns the value of the state column.

Another typical example of using the COALESCEfunction is to substitute the value in one column with another when the first one is NULL.

Suppose you have an articles table with the following structure:

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    excerpt TEXT,
    body TEXT NOT NULL,
    published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)

Let’s insert some data into the articles table.

INSERT INTO articles(title,excerpt,body)
VALUES('MySQL COALESCE Tutorial','This tutorial is about MySQL COALESCE function', 'all about COALESCE function'),
      ('MySQL 8.0 New Features',null, 'The following is a list of new features in MySQL 8.0');
Code language: SQL (Structured Query Language) (sql)

Imagine you have to display articles on an overview page where each article contains the title, expert, and publish date (and also the read more link to the article page). The first task you need to do is to query this data from the articles table:

SELECT
    id, title, excerpt, published_at
FROM
    articles;
Code language: SQL (Structured Query Language) (sql)

As you see the article with id 2 does not have the excerpt, which is not nice for displaying.

A typical solution is to get the first number of characters in the body of the article for displaying as the excerpt. This is why the COALESCE function comes into play.

SELECT
    id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROM
    articles;
Code language: SQL (Structured Query Language) (sql)

In this example, if the value in the excerpt column is NULL, the COALESCE function returns the first 150 characters of the content in the body column.

MySQL COALESCE and CASE expression

Besides using the COALESCE function, you can use the CASE expression to achieve the same effect.

The following query uses the CASE expression to achieve the same result as the example above:

SELECT
    id,
    title,
    (CASE
        WHEN excerpt IS NULL THEN LEFT(body, 150)
        ELSE excerpt
    END) AS excerpt,
    published_at
FROM
    articles;
Code language: SQL (Structured Query Language) (sql)

In this example, the CASE expression is more lengthy than using the COALESCE function.

COALESCE vs. IFNULL

The IFNULL function takes two arguments and returns the first argument if it is not NULL, otherwise, it returns the second argument.

The IFNULL function works great with two arguments whereas the COALESCE function works with narguments. In case the number of arguments is two, both functions are the same.

In this tutorial, you have learned how to use the MySQL COALESCE function to substitute NULL values.

🧾 MySQL COALESCE() and IFNULL() Functions – Complete Study Note

📘 1. Why Do We Need to Handle NULLs in SQL?

In SQL, NULL represents a missing, unknown, or inapplicable value. This can cause problems in:

• Calculations: NULL + 5 = NULL
• Display: Results look incomplete if fields are empty
• Comparisons: NULL = NULL is not true
• Aggregations: SUM(column_with_NULLs) may ignore NULLs

To avoid these issues, SQL provides functions like COALESCE() and IFNULL() to replace or substitute NULL values.

📘 2. Understanding COALESCE() Function

🔹 Definition:

The COALESCE() function takes two or more values and returns the first non-NULL value from the list. If all values are NULL, the result is NULL.

🔹 Syntax:

sql
Copy code
COALESCE(value1, value2, value3, ..., valueN)

• Evaluates from left to right
• Returns the first value that is not NULL

✅ Example 1: Basic Illustration

sql
Copy code
SELECT COALESCE(NULL, NULL, 'Nigeria', 'Ghana');

Explanation:

• All arguments are checked in order.
• Since 'Nigeria' is the first non-NULL value, it is returned.
• Output: 'Nigeria'

🧑‍🎓 3. Real-Life Example with a customers Table

Suppose you have a customers table:

Now, you want to ensure the state column never displays as NULL. You can use COALESCE():

sql
Copy code
SELECT
    customerName,
    city,
    COALESCE(state, 'N/A') AS state,
    country
FROM
    customers;

Explanation:

• For customers with missing states, 'N/A' will appear.
• Helps in cleaner output for reports or applications.

📚 4. COALESCE() with Fallback Logic – articles Table Example

Let’s say you're designing a blog and have an articles table:

sql
Copy code
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    excerpt TEXT,
    body TEXT,
    published_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

You insert some articles:

sql
Copy code
INSERT INTO articles(title, excerpt, body)
VALUES
('MySQL Tutorial', NULL, 'Learn how to use COALESCE in MySQL...'),
('SQL Basics', 'Short summary of SQL...', 'This is the full body...');

Now, to display a summary for each article, you want to use excerpt if available, or fall back to the first 100 characters of the body:

sql
Copy code
SELECT
    title,
    COALESCE(excerpt, LEFT(body, 100)) AS display_excerpt,
    published_at
FROM
    articles;

Explanation:

• COALESCE(excerpt, LEFT(body, 100)): Uses excerpt unless it's NULL, in which case it extracts a snippet from body.

🎓 5. Example with students Table

Consider a students table where some students don’t have a middle name:

You want to create full names for each student:

sql
Copy code
SELECT
    student_id,
    CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name
FROM
    students;

Explanation:

• If middle_name is NULL, '' (empty string) is used.
• Ensures no awkward "John Doe" outputs with extra space.

🛍️ 6. Example with sales Table

You want to calculate net amount = amount - discount, but handle missing discounts:

sql
Copy code
SELECT
    sale_id,
    product,
    amount,
    COALESCE(discount, 0) AS discount,
    amount - COALESCE(discount, 0) AS net_amount
FROM
    sales;

Explanation:

• If discount is missing, treat it as 0.

🔄 7. Can You Use CASE Instead of COALESCE()?

Yes, here’s how it would look:

sql
Copy code
SELECT
    student_id,
    CASE
        WHEN middle_name IS NULL THEN CONCAT(first_name, ' ', last_name)
        ELSE CONCAT(first_name, ' ', middle_name, ' ', last_name)
    END AS full_name
FROM
    students;

Comparison:

• CASE works but is more verbose.
• COALESCE() is cleaner and easier to use.

⚖️ 8. COALESCE() vs IFNULL()

🔸 IFNULL() is a simpler version of COALESCE() that only works with two arguments.

🔹 Syntax:

sql
Copy code
IFNULL(expr1, expr2)

• Returns expr1 if it's not NULL
• Otherwise, returns expr2

✅ Example 1: Basic Usage

sql
Copy code
SELECT IFNULL(NULL, 'Default');  -- Returns 'Default'
SELECT IFNULL('Teslim', 'Default'); -- Returns 'Teslim'

✅ Example 2: Using with sales Table

sql
Copy code
SELECT
    sale_id,
    amount,
    IFNULL(discount, 0) AS discount,
    amount - IFNULL(discount, 0) AS net_amount
FROM
    sales;

Same result as with COALESCE(), but limited to two arguments.

✅ Example 3: Using with students

sql
Copy code
SELECT
    student_id,
    first_name,
    IFNULL(middle_name, '') AS middle_name,
    last_name
FROM
    students;

🧾 Comparison Summary Table

🧠 Key Takeaways

• Use COALESCE() when:
• You have multiple values to check.
• You want more flexibility and cleaner fallback logic.
• You want SQL-standard code (portable to other databases).
• Use IFNULL() when:
• You only need to check two values.
• You want simple syntax.
• Both help to make your SQL queries:
• More robust
• Cleaner in output
• Better suited for real-world applications

Let me know if you'd like this note exported as .md (Markdown), .pdf, or .sql, or if you want to include visuals or charts in your study notes.