SQL Programming Resources on Telegram: “🚀 SQL Roadmap 2026

🚀 SQL Roadmap 2026 — Part 8

NULL Handling, COALESCE & NULLIF — Managing Missing Data in SQL

In real-world databases, missing data is extremely common.

Customers may not have a phone number.

Orders may not have a discount.

Employees may not have a resignation date.

Transactions may have missing reference values.

SQL uses NULL to represent an unknown or missing value.

Understanding NULL properly is essential for accurate SQL queries and data analysis.

🧠 1. What is NULL?

"NULL" means:

«The value is missing, unknown, or not available.»

Example:

customer_id | customer_name | phone
101         | Alice         | 9876543210
102         | Bob           | NULL
103         | Charlie       | 9123456780

Bob's phone number is not stored.

It does not necessarily mean:

• "0"

• empty string "''"

• "Unknown"

• "N/A"

These are different values.

⚠️ 2. NULL Is Not Equal to 0

SELECT *
FROM customers
WHERE credit_limit = 0;

This finds customers whose credit limit is actually zero.

It will not find customers whose credit limit is missing.

To find missing values:

SELECT *
FROM customers
WHERE credit_limit IS NULL;

⚠️ 3. Never Use = NULL

This is incorrect:

SELECT *
FROM customers
WHERE phone = NULL;

It won't correctly identify NULL values.

Use:

SELECT *
FROM customers
WHERE phone IS NULL;

And for non-NULL values:

SELECT *
FROM customers
WHERE phone IS NOT NULL;

Remember:

= NULL       ❌
<> NULL      ❌
IS NULL      ✅
IS NOT NULL  ✅

🔢 4. NULL in Calculations

Suppose:

order_id | price | discount
1        | 1000  | 100
2        | 800   | NULL

Now:

SELECT
    price,
    discount,
    price - discount AS final_price
FROM orders;

For order 2, the result may be:

NULL

because:

800 - NULL = NULL

SQL generally cannot determine the result when one operand is unknown.

🛠️ 5. COALESCE()

COALESCE() is one of the most important functions for handling NULL values.

It returns the first non-NULL value.

Syntax:

COALESCE(value1, value2, value3, ...)

Example:

SELECT
    customer_name,
    COALESCE(phone, 'Not Available') AS phone
FROM customers;

If "phone" is NULL:

NULL → Not Available

🎯 6. COALESCE with Multiple Values

You can provide several fallback values.

SELECT
    customer_name,
    COALESCE(phone, email, 'No Contact Information') AS contact
FROM customers;

SQL checks in order:

phone
  ↓
email
  ↓
No Contact Information

The first non-NULL value is returned.

💰 7. COALESCE for Financial Calculations

Suppose discounts can be NULL.

Instead of:

SELECT
    price - discount AS final_price
FROM orders;

Use:

SELECT
    price - COALESCE(discount, 0) AS final_price
FROM orders;

Now a missing discount is treated as zero.

Example:

Price | Discount | Final Price
1000  | 100      | 900
800   | NULL     | 800

This is extremely common in analytics.

📊 8. COALESCE with Aggregations

Suppose there are no matching transactions for a customer.

You may want to display:

instead of NULL.