NULLIF function in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NULLIF(expr1, expr2) function do in MySQL?

It compares expr1 and expr2. If they are equal, it returns NULL. Otherwise, it returns expr1.

beginner

What will NULLIF(5, 5) return?

It returns NULL because both values are equal.

beginner

What will NULLIF('apple', 'orange') return?

It returns 'apple' because the two values are not equal.

intermediate

Why is NULLIF useful in SQL queries?

It helps avoid division by zero or handle special cases by returning NULL when two values match, which can prevent errors or unwanted results.

intermediate

How does NULLIF differ from a simple CASE statement?

While CASE can do many checks, NULLIF is a shortcut for returning NULL if two expressions are equal, making code shorter and clearer.

What does NULLIF(10, 10) return?

A NULL

B 10

C 0

D Error

NULLIF returns NULL when both arguments are equal.

If NULLIF('cat', 'dog') is used, what is the result?

A 'dog'

B NULL

C 'cat'

D Error

Since the two values are different, NULLIF returns the first value, 'cat'.

Which of these is a common use of NULLIF?

A To replace NULL values with zero

B To return NULL when two values are equal

C To concatenate two strings

D To count rows in a table

NULLIF returns NULL if the two expressions are equal.

What will SELECT NULLIF(0, 0) + 1; return?

A Error

B 1

C 0

D NULL

Since both values are equal, NULLIF returns NULL. Adding 1 to NULL results in NULL.

Which SQL function is a shortcut for: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END?

A NULLIF

B ISNULL

C IFNULL

D COALESCE

NULLIF(expr1, expr2) does exactly that: returns NULL if equal, else expr1.

Explain how the NULLIF function works and give a simple example.

Think about comparing two values and what happens if they match.

Describe a practical situation where using NULLIF can help avoid errors in SQL queries.

Consider what happens if you divide by zero or want to ignore certain values.