MySQL NULL Functions

🚫 MySQL NULL Functions – Complete Tutorial (Beginner → Interview Level)

In MySQL, NULL represents missing or unknown data.
MySQL provides special NULL-handling functions to test, replace, and safely work with NULL values in queries.

1️⃣ What is NULL in MySQL?

  • NULL means no value

  • It is not 0

  • It is not an empty string ''

📌 Any comparison with NULL using = or != returns UNKNOWN.

2️⃣ Why NULL Functions Are Needed?

✔ Handle missing data safely
✔ Avoid wrong calculations
✔ Produce cleaner reports
✔ Prevent runtime errors

3️⃣ IS NULL / IS NOT NULL ⭐ (Most Important)

Check for NULL



 

Check for NOT NULL



 

📌 ❌ marks = NULL ❌ (wrong)
📌 ✔ marks IS NULL ✔ (correct)

4️⃣ IFNULL() Function ⭐

Replaces NULL with a specified value.

Syntax

IFNULL(expression, replacement)

Example

SELECT name, IFNULL(marks, 0) AS marks
FROM students;

✔ If marks is NULL → shows 0

5️⃣ COALESCE() Function ⭐

Returns the first non-NULL value from a list.

Syntax

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

Example

SELECT name,


 

✔ More powerful than IFNULL()

6️⃣ NULLIF() Function

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

Syntax

NULLIF(expr1, expr2)

Example


 

📌 Often used to avoid divide-by-zero


 

7️⃣ IF() with NULL


 

8️⃣ NULL in Aggregate Functions ⭐

Aggregate functions ignore NULL values (except COUNT(*)).



 

9️⃣ NULL with JOINs (Important)

  • NULLs can affect JOIN results

  • Use LEFT JOIN to keep unmatched rows



 

🔟 Comparison Summary ⭐

Function Purpose
IS NULL Check NULL
IS NOT NULL Check non-NULL
IFNULL() Replace NULL
COALESCE() First non-NULL
NULLIF() NULL if equal

1️⃣1️⃣ Common Mistakes ❌

❌ Using = with NULL
❌ Forgetting NULLs in calculations
❌ Confusing '' with NULL
❌ Using COUNT(column) expecting NULL count

📌 Interview Questions & MCQs (Very Important)

Q1. What does NULL represent?

A) Zero
B) Empty string
C) Missing value
D) False

✅ Answer: C

Q2. Which is correct to check NULL?

A) = NULL
B) != NULL
C) IS NULL
D) NULL()

✅ Answer: C

Q3. Which function replaces NULL?

A) COALESCE()
B) IFNULL()
C) Both A and B
D) NULLIF()

✅ Answer: C

Q4. Which function returns first non-NULL value?

A) IF()
B) IFNULL()
C) NULLIF()
D) COALESCE()

✅ Answer: D

Q5. What does COUNT(column) do with NULL?

A) Counts NULL
B) Ignores NULL
C) Throws error
D) Replaces NULL

✅ Answer: B

Q6. Which function helps avoid divide by zero?

A) IFNULL()
B) COALESCE()
C) NULLIF()
D) COUNT()

✅ Answer: C

🔥 Real-Life Use Cases

✔ Handling missing marks
✔ Default values in reports
✔ Safe calculations
✔ Optional user data
✔ Data cleaning

✅ Summary

  • NULL means missing value

  • Use IS NULL / IS NOT NULL

  • IFNULL() & COALESCE() replace NULL

  • NULLIF() avoids errors

  • Aggregates ignore NULL

  • Very important for SQL exams & interviews

You may also like...