CodeCapsule

SQL NULL Functions

by

Here is a clear and practical guide to the most important SQL NULL-handling functions, including examples for PostgreSQL, MySQL, SQL Server, and Oracle.

✅ Why NULL Functions Matter

In SQL, NULL means “unknown or missing value.”
Most expressions and comparisons return NULL when NULL is involved, so 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)

SELECT ISNULL(email, 'no-email@example.com')
FROM users;

Equivalent to COALESCE() but only takes two arguments.

🔹 3. IFNULL() (MySQL only)

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

Same purpose as ISNULL() in SQL Server.

🔹 4. NVL() (Oracle only)

SELECT NVL(city, 'Unknown City')
FROM addresses;

Oracle’s version of NULL replacement.

🔹 5. NULLIF() (ANSI Standard)

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

SELECT NULLIF(score, 0) AS safe_score
FROM results;

Useful to avoid division by zero:

SELECT total / NULLIF(quantity, 0) AS avg_value
FROM sales;

🔥 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

SELECT COALESCE(salary, 0) AS salary
FROM employees;

Default country when missing

SELECT COALESCE(country, 'Unknown') AS country
FROM customers;

Count only non-NULL values (using CASE)

SELECT COUNT(CASE WHEN email IS NOT NULL THEN 1 END) AS emails_present
FROM users;

Avoid division-by-zero with NULLIF

SELECT amount / NULLIF(quantity, 0) AS unit_price
FROM products;

🧠 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

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *