SQL Null Functions

SQL NULL Functions

NULL functions help handle missing values in SQL queries. They allow you to replace or test NULL values safely.

COALESCE (Standard SQL)

COALESCE returns the first non-NULL value in a list.

SELECT name, COALESCE(phone, 'N/A') AS phone
FROM customers;

COALESCE is part of the SQL standard and works in most database systems.

IFNULL (MySQL / MariaDB)

IFNULL returns a replacement value if the expression is NULL.

SELECT name, IFNULL(phone, 'N/A') AS phone
FROM customers;

ISNULL (SQL Server)

In SQL Server, ISNULL replaces NULL with a specified value.

SELECT name, ISNULL(phone, 'N/A') AS phone
FROM customers;

NVL (Oracle)

Oracle uses NVL for similar behavior.

SELECT name, NVL(phone, 'N/A') AS phone
FROM customers;

NULLIF

NULLIF returns NULL if two expressions are equal; otherwise, it returns the first expression.

SELECT NULLIF(discount, 0) AS discount_value
FROM products;

This is useful to avoid division-by-zero errors.

NULL in Arithmetic

Arithmetic operations involving NULL return NULL.

SELECT price - discount AS final_price
FROM products;

If discount is NULL, final_price will also be NULL unless handled with COALESCE.

Best Practice

• Use COALESCE for portability
• Handle NULL explicitly in calculations
• Understand how your database treats NULL comparisons

Next Step

Continue with SQL Stored Procedures to learn how to define reusable database logic.