Tutorials›SQL›Handling NULL Values
🟢 Free Demo
SQL TutorialTopic 16 of 20

Handling NULL Values

IS NULL, IS NOT NULL, COALESCE, ISNULL

✅ What You Will Learn

›What NULL means in SQL and why it is not zero or empty string
›How to test for NULL with IS NULL and IS NOT NULL
›How COALESCE replaces NULL with a default value
›How NULLIF returns NULL when two values are equal
›How NULLs affect COUNT, SUM, AVG, and comparisons

NULL in SQL means "no value" or "unknown". It is not zero, not an empty string — it represents the complete absence of data. NULLs are common in real-world datasets and handling them correctly is a core analyst skill.

Comparing NULL with = will not work — you must use IS NULL. And NULLs in calculations produce NULL results, which can silently break reports.

📋 customers table — some rows have NULL in the discount column

customer_idcustomer_namecitydiscount
1Rahul SharmaDelhi500
2Priya VermaNoidaNULL
3Amit KumarGurgaon200
4Sneha KapoorDelhiNULL
5Vikram SinghNoida150

Syntax

SQL SYNTAX
IS NULL          -- check if value is NULL
IS NOT NULL      -- check if value exists
COALESCE(a, b)   -- return first non-NULL value
ISNULL(a, b)     -- SQL Server: replace NULL with b
IFNULL(a, b)     -- MySQL: replace NULL with b

Examples

Example 1Find rows with missing data
-- Find orders where discount is not recorded
SELECT customer_name, product, amount
FROM orders
WHERE discount IS NULL;
💡

WHERE discount = NULL does NOT work in SQL. Always use IS NULL. This trips up beginners constantly.

Example 2COALESCE — replace NULL with a default
SELECT customer_name,
       amount,
       COALESCE(discount, 0) AS discount,
       amount - COALESCE(discount, 0) AS final_amount
FROM orders;
💡

COALESCE returns the first non-NULL value from its arguments. If discount is NULL, COALESCE(discount, 0) returns 0 — keeping the calculation valid.

Example 3NULL in aggregations
-- COUNT(*) includes NULLs, COUNT(column) excludes them
SELECT COUNT(*) AS total_rows,
       COUNT(discount) AS rows_with_discount,
       AVG(discount) AS avg_discount
FROM orders;
💡

AVG automatically ignores NULLs — it divides only by the count of non-NULL values. Be aware of this when calculating averages on sparse columns.

📌 Key Points to Remember

  • ✓NULL is not zero and not empty string — it means no value
  • ✓Use IS NULL and IS NOT NULL — never = NULL
  • ✓NULLs propagate in calculations: NULL + 5 = NULL
  • ✓COALESCE works across all databases — prefer it over ISNULL or IFNULL
  • ✓COUNT(*) includes NULLs; COUNT(column) does not

🏢 Real-World Application

NULL handling is one of the most practical skills for a data analyst working with real-world data. Databases always have missing data — a customer who did not provide their phone number, an order with no discount, a survey response left blank. Knowing how to handle NULLs prevents silent errors in reports. COALESCE(discount, 0) ensures a SUM of discounts does not return NULL when one customer has no discount. IS NULL is used to find incomplete records: WHERE email IS NULL finds all customers missing email addresses — essential for data quality checks.

⚠️ Common Mistakes to Avoid

WRONGComparing NULL with = (WHERE col = NULL)
FIXNULL = NULL is always false in SQL. Use IS NULL: WHERE col IS NULL. Similarly, use IS NOT NULL instead of <> NULL or != NULL.
WRONGExpecting NULL to be treated as 0 or empty string in calculations
FIXAny arithmetic with NULL returns NULL: 100 + NULL = NULL. Use COALESCE(col, 0) to convert NULL to 0 before arithmetic operations.
WRONGNot accounting for NULLs in COUNT
FIXCOUNT(col) excludes NULLs; COUNT(*) includes them. If your column has NULLs and you want to count all rows, use COUNT(*). If you want to know how many non-null values exist, use COUNT(col).

❓ Frequently Asked Questions

What is NULL in SQL?

NULL represents a missing or unknown value. It is not zero, not empty string, not false — it is the absence of any value. A column is NULL when no data was entered for that field.

How do I check for NULL in SQL?

Use IS NULL or IS NOT NULL. WHERE phone IS NULL finds rows with no phone number. WHERE phone IS NOT NULL finds rows that have a phone number. Never use = NULL or != NULL — they always return no results.

What does COALESCE do in SQL?

COALESCE returns the first non-NULL value in a list. COALESCE(discount, 0) returns the discount if it exists, or 0 if it is NULL. COALESCE(col1, col2, col3, 'default') returns the first non-NULL among the three columns, or 'default' if all are NULL.

What is NULLIF in SQL?

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It is useful for avoiding division-by-zero errors: amount / NULLIF(quantity, 0) returns NULL instead of an error when quantity is 0.

How do NULLs affect aggregate functions?

COUNT(*) counts NULLs. COUNT(col) ignores NULLs. SUM, AVG, MIN, MAX all ignore NULLs. So AVG of [10, 20, NULL] is 15 (not 10). Be aware of this when NULLs represent zero rather than missing data.

✏️ Practice Exercise

Write a query that shows each customer's name and discount amount — replace any NULL discount with 0 and label that column "applied_discount".

← PreviousCASE StatementNext →String Functions
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.