COALESCE vs ISNULL

Editorial

Introduction

When working with NULL values, you often need to replace them with meaningful default values. SQL provides functions to handle this: COALESCE (standard SQL) and ISNULL (SQL Server specific).

In this tutorial, you will learn:

How COALESCE works and its syntax
How ISNULL works (SQL Server)
Key differences between the two
SQLite alternatives for NULL handling
When to use each approach

COALESCE Function

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

Syntax

COALESCE(expression1, expression2, ..., expressionN)

How It Works

Evaluates expressions from left to right
Returns the first expression that is NOT NULL
If all expressions are NULL, returns NULL

Key Features

Feature	Description
ANSI Standard	Works in all major databases
Multiple arguments	Can accept 2 or more expressions
Type consistency	All expressions should be compatible types
Short-circuit	Stops evaluating after finding non-NULL

-- COALESCE basic examples
SELECT 
    COALESCE(NULL, 'Default') AS single_null,
    COALESCE('Value', 'Default') AS no_null,
    COALESCE(NULL, NULL, 'Third') AS multiple_nulls,
    COALESCE(NULL, NULL, NULL) AS all_null;

Side-by-side comparison of COALESCE and ISNULL showing how each replaces NULL values with defaults, with key differences highlighted

ISNULL Function (SQL Server)

ISNULL is a SQL Server-specific function that replaces NULL with a specified replacement value.

Syntax

ISNULL(expression, replacement_value)

How It Works

Checks if the expression is NULL
If NULL, returns the replacement value
If NOT NULL, returns the original expression

Key Features

Feature	Description
SQL Server only	Not available in SQLite, MySQL, PostgreSQL
Two arguments only	Unlike COALESCE, accepts exactly 2 values
Data type	Returns data type of first argument
Performance	Slightly faster than COALESCE in SQL Server

SQL Server Example (for reference)

-- SQL Server ISNULL syntax
SELECT ISNULL(NULL, 'Default');      -- Returns 'Default'
SELECT ISNULL('Value', 'Default');   -- Returns 'Value'

SQLite: IFNULL and COALESCE

SQLite supports COALESCE (standard SQL) and IFNULL (SQLite-specific, similar to ISNULL).

-- SQLite NULL handling functions
SELECT 
    -- COALESCE - returns first non-NULL
    COALESCE(NULL, 'Fallback') AS coalesce_result,
    COALESCE(NULL, NULL, 'Third Option') AS coalesce_multiple,
    
    -- IFNULL - SQLite's version of ISNULL (2 arguments)
    IFNULL(NULL, 'Fallback') AS ifnull_result,
    IFNULL('Original', 'Fallback') AS ifnull_not_null;

Practical Example: Replacing NULL Delivery Dates

Let's use COALESCE to replace NULL delivery dates with a meaningful message:

-- Replace NULL delivery dates with status message
SELECT 
    order_id,
    order_status,
    order_purchase_timestamp,
    order_delivered_customer_date,
    COALESCE(order_delivered_customer_date, 'Not Delivered Yet') AS delivery_display
FROM olist_orders_dataset
WHERE order_status IN ('shipped', 'processing', 'delivered')
LIMIT 15;

COALESCE with Multiple Fallbacks

COALESCE shines when you need a cascade of fallback values:

-- Multiple fallback values for delivery date
SELECT 
    order_id,
    order_status,
    -- Try delivery date, then carrier date, then approval date, then message
    COALESCE(
        order_delivered_customer_date,
        order_delivered_carrier_date,
        order_approved_at,
        'Pending'
    ) AS best_available_date
FROM olist_orders_dataset
LIMIT 15;

This is where COALESCE excels over ISNULL - you can chain multiple fallbacks in a single function call.

COALESCE vs ISNULL: Key Differences

Comparison Table

Feature	COALESCE	ISNULL (SQL Server)
SQL Standard	✅ Yes (ANSI)	❌ No (proprietary)
Arguments	2 or more	Exactly 2
Works in SQLite	✅ Yes	❌ No (use IFNULL)
Data type	Highest precedence type	First argument type
Nested calls	Not needed	Required for 3+ values
Performance	Standard	Slightly faster in SQL Server

When to Use Each

Use COALESCE when:

Writing portable SQL that works across databases
You need more than 2 fallback values
You want standard-compliant code

Use ISNULL/IFNULL when:

Working exclusively in SQL Server/SQLite
You only need one fallback value
You need specific data type behavior

-- Demonstrating the difference with IFNULL vs COALESCE
SELECT 
    order_id,
    order_delivered_customer_date,
    
    -- IFNULL: Only 2 arguments
    IFNULL(order_delivered_customer_date, 'N/A') AS using_ifnull,
    
    -- COALESCE: Can chain multiple
    COALESCE(
        order_delivered_customer_date, 
        order_delivered_carrier_date,
        'In Transit'
    ) AS using_coalesce
FROM olist_orders_dataset
WHERE order_status != 'delivered'
LIMIT 10;

Replacing NULL with Default Numbers

COALESCE is commonly used to replace NULL numeric values with zero or other defaults:

-- Replace NULL freight values with 0 for calculations
SELECT 
    oi.order_id,
    oi.product_id,
    oi.price,
    oi.freight_value,
    -- Replace NULL freight with 0
    COALESCE(oi.freight_value, 0) AS freight_safe,
    -- Calculate total with NULL-safe freight
    oi.price + COALESCE(oi.freight_value, 0) AS total_price
FROM olist_order_items_dataset oi
LIMIT 15;

COALESCE in Aggregations

Use COALESCE to provide meaningful values when aggregations return NULL:

-- Handling NULL in aggregation results
SELECT 
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS order_count,
    -- AVG might return NULL if no orders
    COALESCE(AVG(oi.price), 0) AS avg_price,
    -- SUM might return NULL for empty groups
    COALESCE(SUM(oi.price), 0) AS total_sales
FROM olist_customers_dataset c
LEFT JOIN olist_orders_dataset o ON c.customer_id = o.customer_id
LEFT JOIN olist_order_items_dataset oi ON o.order_id = oi.order_id
WHERE c.customer_state IN ('SP', 'RJ', 'MG')
GROUP BY c.customer_state
ORDER BY total_sales DESC;

Building Display-Friendly Values

COALESCE helps create user-friendly output by replacing NULL with readable text:

-- Create display-friendly order status report
SELECT 
    order_id,
    order_status,
    COALESCE(date(order_purchase_timestamp), 'Unknown') AS purchase_date,
    COALESCE(date(order_approved_at), 'Pending Approval') AS approval_date,
    COALESCE(date(order_delivered_carrier_date), 'Not Shipped') AS ship_date,
    COALESCE(date(order_delivered_customer_date), 'Not Delivered') AS delivery_date
FROM olist_orders_dataset
LIMIT 15;

Practice: NULL Replacement

Try using COALESCE to create a comprehensive order summary:

-- Practice: Create NULL-safe order summary
SELECT 
    o.order_id,
    o.order_status,
    -- Replace NULL review scores with 'No Review'
    COALESCE(CAST(r.review_score AS TEXT), 'No Review') AS review,
    -- Replace NULL payment values with 0
    COALESCE(p.payment_value, 0) AS payment,
    -- Cascade through date fields
    COALESCE(
        date(o.order_delivered_customer_date),
        date(o.order_delivered_carrier_date),
        date(o.order_approved_at),
        date(o.order_purchase_timestamp)
    ) AS latest_known_date
FROM olist_orders_dataset o
LEFT JOIN olist_order_reviews_dataset r ON o.order_id = r.order_id
LEFT JOIN olist_order_payments_dataset p ON o.order_id = p.order_id
LIMIT 15;

Summary

Key Takeaways

COALESCE Syntax

COALESCE(value1, value2, ..., valueN)
-- Returns first non-NULL value

ISNULL/IFNULL Syntax

ISNULL(value, replacement)   -- SQL Server
IFNULL(value, replacement)   -- SQLite/MySQL

Key Differences

Aspect COALESCE ISNULL/IFNULL
Standard ANSI SQL Database-specific
Arguments Multiple Two only
Portability High Limited
Common Use Cases
- Replace NULL with default text: COALESCE(name, 'Unknown')
- Replace NULL with 0 for math: COALESCE(price, 0)
- Cascade through fallbacks: COALESCE(a, b, c, 'default')
Best Practice
- Use COALESCE for portable code
- Use IFNULL in SQLite when you only need 2 values
- Always handle NULLs before displaying to users

Aspect	COALESCE	ISNULL/IFNULL
Standard	ANSI SQL	Database-specific
Arguments	Multiple	Two only
Portability	High	Limited

What's Next?

In the next lesson, we'll explore Handling NULLs in Data Aggregation - how aggregate functions like COUNT, SUM, and AVG behave with NULL values.