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.

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.

 Why NULL Functions Are Needed?

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

 IS NULL / IS NOT NULL (Most Important)

Check for NULL

Check for NOT NULL

•   marks = NULL  (wrong)
•   marks IS NULL  (correct)

 IFNULL() Function

Replaces NULL with a specified value.

Syntax

Example

•  If marks is NULL → shows 0

 COALESCE() Function

Returns the first non-NULL value from a list.

Syntax

Example

•  More powerful than IFNULL()

 NULLIF() Function

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

Syntax

Example

•  Often used to avoid divide-by-zero

 IF() with NULL

 NULL in Aggregate Functions

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

NULL with JOINs (Important)

• NULLs can affect JOIN results

• Use LEFT JOIN to keep unmatched rows

 Comparison Summary

 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