COALESCE

1. What Is COALESCE?

COALESCE is an SQL function that returns the first non-NULL value from a list of expressions.

In simple terms:

“If the first value is NULL, use the next one.”

2. Syntax

COALESCE(value1, value2, value3, ...)

COALESCE(value1, value2, value3, ...)

• Values are checked from left to right
• The first value that is not NULL is returned
• If all values are NULL, the result is NULL

3. Basic Examples

Example 1: Simple NULL handling

SELECT COALESCE(NULL, 'A', 'B');

SELECT COALESCE(NULL, 'A', 'B');

Result:

A

A

Example 2: Column-level usage

SELECT
  COALESCE(product_name, product_code) AS product_info
FROM customer_purchase;

SELECT
  COALESCE(product_name, product_code) AS product_info
FROM customer_purchase;

Meaning:

• Use product_name if it exists
• If product_name is NULL, use product_code

This improves readability and completeness of results.

4. Common Real-World Use Cases

1) Replacing NULL with a default value

SELECT
  COALESCE(discount, 0) AS discount
FROM sales;

SELECT
  COALESCE(discount, 0) AS discount
FROM sales;

• Prevents NULL values from breaking calculations

2) Preventing NULL results in calculations

SELECT
  price * COALESCE(quantity, 1) AS total_price
FROM orders;

SELECT
  price * COALESCE(quantity, 1) AS total_price
FROM orders;

• If quantity is NULL, treat it as 1

3) Choosing from multiple fallback columns

SELECT
  COALESCE(phone_mobile, phone_home, phone_work) AS contact_number
FROM customers;

SELECT
  COALESCE(phone_mobile, phone_home, phone_work) AS contact_number
FROM customers;

Priority order:

1. Mobile phone
2. Home phone
3. Work phone

Very common in production databases.

4) Using COALESCE in WHERE clauses

SELECT *
FROM orders
WHERE COALESCE(status, 'unknown') = 'completed';

SELECT *
FROM orders
WHERE COALESCE(status, 'unknown') = 'completed';

• Treats NULL status values as 'unknown' for filtering

5. COALESCE vs Other NULL Functions

COALESCE is the most portable and recommended option.

6. Important Considerations

Data types must be compatible

COALESCE(price, 'N/A')  -- may cause an error

COALESCE(price, 'N/A')  -- may cause an error

Correct approach:

COALESCE(CAST(price AS STRING), 'N/A')

COALESCE(CAST(price AS STRING), 'N/A')

All arguments should be of the same or compatible data type.

7. Why COALESCE Is Important

COALESCE helps:

• Handle missing data safely
• Avoid calculation errors
• Improve query output readability
• Produce consistent, analysis-ready results

8. One-Line Summary

COALESCE returns the first non-NULL value and is one of the most essential SQL functions for clean, reliable data analysis.