SQL Null Functions

In SQL, NULL represents missing, unknown, or unrecorded data. Because NULL is not a value (it signifies the absence of a value), standard arithmetic operations involving NULL yield NULL (e.g., 5 + NULL = NULL).

SQL Null Functions allow you to substitute NULL values with fallback defaults, evaluate alternative expressions, or safely handle missing data without breaking calculations or application logic.

Core SQL Null Functions

While different relational databases (PostgreSQL, MySQL, SQL Server, Oracle) offer proprietary vendor functions, standard SQL provides built-in functions to handle missing values seamlessly.

1. COALESCE() — Standard & Portable

The COALESCE() function is part of the official ANSI SQL standard and is supported across almost all database engines (PostgreSQL, MySQL, SQL Server, Oracle, SQLite).

It evaluates arguments sequentially from left to right and returns the first non-null value in the list. If all arguments evaluate to NULL, COALESCE() returns NULL.

Syntax

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

Example

Suppose an Employees table contains fields for MobilePhone, WorkPhone, and HomePhone:

SELECT 
    FirstName,
    LastName,
    COALESCE(MobilePhone, WorkPhone, HomePhone, 'No Phone Provided') AS PrimaryContact
FROM Employees;

Query Execution Flow:

• If MobilePhone is present ---> Return MobilePhone.
• If MobilePhone is NULL --> Check WorkPhone.
• If WorkPhone is present --> Return WorkPhone.
• If all phone fields are NULL --> Fall back to 'No Phone Provided'.

2. NULLIF() — Preventing Division by Zero

The NULLIF() function compares two arguments:

• Returns NULL if expression1 equals expression2.
• Returns expression1 if they are not equal.

Syntax

NULLIF(expression1, expression2)

Primary Use Case: Safe Division

Dividing by zero causes a database runtime error. Combining COALESCE() with NULLIF() allows you to handle zero-divisor scenarios safely without throwing errors:

SELECT 
    DepartmentName,
    TotalRevenue,
    TotalUnits,
    -- Returns NULL if TotalUnits = 0, avoiding a Division by Zero error
    COALESCE(TotalRevenue / NULLIF(TotalUnits, 0), 0) AS RevenuePerUnit
FROM DepartmentSales;

Vendor-Specific Null Functions

Most RDBMS platforms provide proprietary, two-argument null functions designed to replace a single NULL with a default value.

                  RDBMS VENDOR-SPECIFIC NULL FUNCTIONS
                  
   +------------------------------------------------------------------+
   |  SQL Server  |    MySQL     |     Oracle     |    PostgreSQL   |
   +--------------+--------------+----------------+-----------------+
   | ISNULL(a, b) | IFNULL(a, b) | NVL(a, b)      | COALESCE(a, b)  |
   |              | NVL(a, b)*   | NVL2(a, b, c)  |                 |
   +------------------------------------------------------------------+
   *Note: MySQL supports IFNULL(); Oracle uses NVL().

Database Engine Function Comparison

Practical Examples & Usage

1. Replacing NULL in Calculations

When calculating product unit margins where discounts might be NULL:

-- Without Null Handling (If Discount is NULL, Total becomes NULL)
SELECT ProductID, (UnitPrice - Discount) AS NetPrice 
FROM Sales;

-- SQL Server Solution
SELECT ProductID, (UnitPrice - ISNULL(Discount, 0)) AS NetPrice 
FROM Sales;

-- MySQL Solution
SELECT ProductID, (UnitPrice - IFNULL(Discount, 0)) AS NetPrice 
FROM Sales;

-- Cross-Platform Standard Solution (Recommended)
SELECT ProductID, (UnitPrice - COALESCE(Discount, 0)) AS NetPrice 
FROM Sales;

2. Handling NULL in Aggregate Functions

Aggregate functions like SUM(), AVG(), MIN(), and MAX() automatically ignore NULL values. However, if a table contains zero rows or all values in a column are NULL, the function returns NULL.

Wrap aggregate results in COALESCE() to guarantee a numeric output:

SELECT 
    CategoryID,
    COALESCE(SUM(UnitsInStock), 0) AS TotalStock,
    COALESCE(AVG(Price), 0.00) AS AveragePrice
FROM Products
WHERE Discontinued = 1
GROUP BY CategoryID;