COALESCE for NULL handling in SQL

Introduction

COALESCE helps you pick the first value that is not empty or missing from a list. It makes sure you get a real value instead of nothing.

When you want to show a default value if some data is missing.

When combining columns that might have empty spots and you want the first filled one.

When you want to avoid errors caused by empty or missing data in calculations.

When you want to clean up reports by replacing missing values with something readable.

Syntax

SQL

COALESCE(value1, value2, ..., valueN)

It checks each value from left to right and returns the first one that is not NULL.

If all values are NULL, then COALESCE returns NULL.

Examples

Returns 'apple' because it is the first non-NULL value.

SQL

SELECT COALESCE(NULL, 'apple', 'banana');

Returns 10 as it is the first non-NULL number.

SQL

SELECT COALESCE(NULL, NULL, 10, 20);

Returns NULL because all values are NULL.

SQL

SELECT COALESCE(NULL, NULL, NULL);

Sample Program

This query picks the first available name or color for each fruit. If both are missing, it shows 'Unknown'.

SQL

CREATE TABLE fruits (id INT, name VARCHAR(20), color VARCHAR(20));
INSERT INTO fruits VALUES (1, 'Apple', NULL), (2, NULL, 'Yellow'), (3, NULL, NULL);
SELECT id, COALESCE(name, color, 'Unknown') AS fruit_name FROM fruits ORDER BY id;

Important Notes

COALESCE is very useful to avoid showing NULL in your results, which can be confusing.

You can use COALESCE with any data type, but all values should be compatible types.

It works like a simple fallback system, checking values one by one.

Summary

COALESCE returns the first non-NULL value from a list.

It helps replace missing data with defaults or alternatives.

Use it to make your query results clearer and more complete.