SQL NULL Functions

SQL Tutorial

Here is a simple and useful guide to the key SQL NULL Functions. It includes examples for PostgreSQL, MySQL, SQL Server, and Oracle.

✅ Why SQL NULL Functions Matter

In SQL, NULL means “unknown or missing value.”
Most expressions and comparisons return NULL when NULL is present. Special functions exist to safely replace or handle NULLs.

🔹 1. COALESCE() (ANSI Standard — works in PostgreSQL, MySQL, SQL Server, Oracle)

Returns the first non-NULL value in the list.

SELECT COALESCE(nickname, name, 'Unknown') AS display_name
FROM users;

✔ Most portable
✔ Most recommended
✔ Handles multiple arguments

🔹 2. ISNULL() (SQL Server only)

Equivalent to COALESCE() but only takes two arguments.

🔹 3. IFNULL() (MySQL only)

Same purpose as ISNULL() in SQL Server.

🔹 4. NVL() (Oracle only)

Oracle’s version of NULL replacement.

🔹 5. NULLIF() (ANSI Standard)

Returns NULL if the two expressions are equal, otherwise returns the first expression.

Useful to avoid division by zero:

🔥 Comparison: COALESCE vs IFNULL vs ISNULL vs NVL

Function DBMS Args Notes
COALESCE All Many Most portable, best practice
IFNULL MySQL 2 Fast, MySQL-specific
ISNULL SQL Server 2 SQL Server-specific
NVL Oracle 2 Oracle-specific
NULLIF All 2 Converts equality to NULL

📌 Practical Examples

Replace NULL salary with default

Default country when missing

Count only non-NULL values (using CASE)

Avoid division-by-zero with NULLIF

🧠 Best Practices

✔ Prefer COALESCE for cross-database code
✔ Use DB-specific functions only when needed
✔ Avoid storing empty strings instead of NULL
✔ Always handle NULL when doing math
✔ Use NULLIF to safely handle “invalid” values

You may also like...