NULLIF function in MySQL - Step-by-Step Execution

Concept Flow - NULLIF function

Start

↓

Evaluate expr1

↓

Evaluate expr2

↓

Compare expr1 and expr2

↓

Return NULL

↓

End

NULLIF compares two expressions; if they are equal, it returns NULL, otherwise it returns the first expression.

Execution Sample

MySQL

SELECT NULLIF(5, 5) AS result1, NULLIF(5, 3) AS result2;

This query returns NULL for the first expression because 5 equals 5, and returns 5 for the second because 5 does not equal 3.

Execution Table

Step	Expression 1	Expression 2	Comparison Result	Return Value
1	5	5	Equal	NULL
2	5	3	Not Equal	5
3	-	-	-	Execution ends

💡 All expressions evaluated; NULLIF returns NULL if expressions are equal, else returns first expression.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
expr1	5	5	5	5
expr2	5 or 3	5	3	3
return_value	undefined	NULL	5	5

Key Moments - 2 Insights

Why does NULLIF(5, 5) return NULL instead of 5?

Because the two expressions are equal (see execution_table row 1), NULLIF returns NULL as per its rule.

What happens if the two expressions are not equal?

NULLIF returns the first expression unchanged (see execution_table row 2), so NULLIF(5, 3) returns 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the return value when expr1=5 and expr2=5?

A 5

B NULL

C 3

D Error

💡 Hint

Check execution_table row 1 under 'Return Value' column.

At which step does NULLIF return the first expression instead of NULL?

A Step 2

B Step 1

C Step 3

D Never

💡 Hint

See execution_table row 2 where expressions are not equal.

If expr1 is 10 and expr2 is 10, what would NULLIF return?

A 10

B 0

C NULL

D Error

💡 Hint

Recall NULLIF returns NULL when both expressions are equal (concept_flow).

Concept Snapshot

NULLIF(expr1, expr2)
- Compares expr1 and expr2
- Returns NULL if equal
- Returns expr1 if not equal
- Useful to avoid division by zero or replace values
- Simple conditional return function

Full Transcript

The NULLIF function in MySQL compares two expressions. If they are equal, it returns NULL. Otherwise, it returns the first expression. For example, NULLIF(5, 5) returns NULL because both values are equal. But NULLIF(5, 3) returns 5 because the values differ. This function helps replace specific values with NULL easily. The execution flow starts by evaluating both expressions, comparing them, and then returning NULL or the first expression based on equality.