Home / SQL / NULL Functions

NULL Functions

Overview

NULL functions are SQL functions designed to handle NULL values. They allow you to replace NULL values with default values, check for NULLs, and perform calculations that would otherwise result in NULL.

Common NULL functions include COALESCE, ISNULL, IFNULL, and NULLIF, which vary by database system.

COALESCE Function

COALESCE returns the first non-NULL value from a list of expressions. It's standard SQL and works in most databases.

COALESCE Syntax

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

Example 1: Replace NULL with Default

Default value for NULL

SELECT 
    name,
    COALESCE(phone, 'N/A') AS phone,
    COALESCE(email, 'No Email') AS email
FROM Customers;

Example 2: Multiple Values

First non-NULL value

SELECT 
    name,
    COALESCE(phone, mobile_phone, work_phone, 'No Contact') AS contact
FROM Customers;

Example 3: Calculations with NULL

Safe calculations

SELECT 
    product_name,
    price,
    discount,
    price * (1 - COALESCE(discount, 0)) AS final_price
FROM Products;

ISNULL Function

ISNULL is SQL Server specific. It replaces NULL with a specified value:

ISNULL (SQL Server)

ISNULL(expression, replacement_value)

-- Example
SELECT 
    name,
    ISNULL(phone, 'N/A') AS phone
FROM Customers;

IFNULL Function

IFNULL is MySQL specific. Similar to ISNULL:

IFNULL (MySQL)

IFNULL(expression, replacement_value)

-- Example
SELECT 
    name,
    IFNULL(phone, 'N/A') AS phone
FROM Customers;

NVL Function

NVL is Oracle specific. Similar to IFNULL/ISNULL:

NVL (Oracle)

NVL(expression, replacement_value)

-- Example
SELECT 
    name,
    NVL(phone, 'N/A') AS phone
FROM Customers;

NULLIF Function

NULLIF returns NULL if two expressions are equal, otherwise returns the first expression:

NULLIF Syntax

NULLIF(expression1, expression2)

-- Example: Convert empty strings to NULL
SELECT 
    name,
    NULLIF(TRIM(phone), '') AS phone
FROM Customers;

-- Example: Prevent division by zero
SELECT 
    product_name,
    quantity,
    CASE 
        WHEN NULLIF(quantity, 0) IS NULL THEN NULL
        ELSE total_value / quantity
    END AS unit_price
FROM Products;

Cross-Database Compatibility

For portability, use COALESCE instead of database-specific functions:

Portable NULL handling

-- Works in all databases
SELECT COALESCE(phone, 'N/A') FROM Customers;

-- Database-specific (not portable)
SELECT ISNULL(phone, 'N/A') FROM Customers;  -- SQL Server only
SELECT IFNULL(phone, 'N/A') FROM Customers;  -- MySQL only
SELECT NVL(phone, 'N/A') FROM Customers;     -- Oracle only

Common Use Cases

Replacing NULL with default values
Handling NULL in calculations
Choosing first available value
Preventing division by zero
Normalizing empty strings to NULL

Best Practices

Use COALESCE: For maximum portability
Choose meaningful defaults: Use appropriate replacement values
Handle all cases: Consider what happens when values are NULL
Document defaults: Make it clear what values are used
Test NULL scenarios: Verify behavior with NULL values

Next Steps

Learn about creating database objects with CREATE DATABASE, or explore other SQL functions and operations.

← Case