🧠 SQL COALESCE Function – Handling NULLs the Smart Way

Tired of seeing NULL in your query results?
Use the SQL COALESCE() function to replace NULLs with meaningful default values – effortlessly.

In this article, you’ll learn how COALESCE() works, when to use it, and how to implement it in real-world queries with practical examples and best practices.

🔍 What is SQL COALESCE?

The COALESCE() function in SQL returns the first non-NULL value from a list of expressions. It’s often used to replace NULL values with fallback defaults like 0, N/A, Unknown, or today’s date.

💡 Think of it as:

“Give me the first value in this list that’s not NULL.”

🧾 SQL COALESCE Syntax

COALESCE(value1, value2, ..., value_n)
  • Evaluates expressions from left to right.
  • Returns the first non-null value found.
  • If all are NULL, returns NULL.

✅ SQL COALESCE Examples

🧑‍💻 Example 1: Replace NULL with a Default Value

SELECT 
  employee_name,
  COALESCE(phone_number, 'Not Provided') AS contact_number
FROM employees;

🔹 If phone_number is NULL, it shows “Not Provided” instead.

📆 Example 2: Replace NULL Dates with Today’s Date

SELECT 
  order_id,
  COALESCE(delivery_date, CURRENT_DATE) AS expected_delivery
FROM orders;

🗓 Useful in logistics & scheduling reports when a delivery date is missing.

💲 Example 3: Use in Arithmetic Operations

SELECT 
  product_id,
  price,
  discount,
  price - COALESCE(discount, 0) AS final_price
FROM products;

💰 This ensures that NULL discounts don’t break the subtraction.

🧠 Use Cases for COALESCE in SQL

  • ✅ Replace NULL with fallback values in SELECT
  • ✅ Clean dirty/missing data in reports
  • ✅ Combine multiple optional columns (e.g., email, alt_email, phone)
  • ✅ Safeguard arithmetic & logic operations from NULL errors
  • ✅ Simplify complex CASE statements

⚠️ Things to Remember

  • All arguments in COALESCE() should be of compatible data types
  • It’s ANSI SQL standard – works across MySQL, PostgreSQL, SQL Server, Oracle, and more
  • If all inputs are NULL, COALESCE() returns NULL
  • Can be used in SELECT, WHERE, ORDER BY, GROUP BY, and even inside CASE statements

📊 Example: Combine Multiple Contact Methods

SELECT 
  customer_id,
  COALESCE(email, alt_email, phone, 'No Contact Info') AS preferred_contact
FROM customers;

👇 Falls back from primary email → alt email → phone → default message.

🔁 COALESCE vs ISNULL vs NVL vs IFNULL

FunctionPlatformDescription
COALESCEANSI SQLReturns first non-null value
ISNULLSQL ServerWorks like COALESCE (2 arguments only)
IFNULLMySQLSimilar to ISNULL
NVLOracleOracle’s equivalent to IFNULL

🧠 Tip: Always prefer COALESCE() for cross-platform compatibility.

📝 Summary

  • The SQL COALESCE() function returns the first non-null value in a list of expressions
  • It is perfect for handling NULLs, setting default values, and cleaning up output
  • Works in all major RDBMS and supports cross-platform SQL writing