COALESCE and NULLIF as CASE shortcuts in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL function COALESCE do?

COALESCE returns the first non-NULL value from a list of expressions. It helps to replace NULLs with a default value.

beginner

How does NULLIF work in SQL?

NULLIF compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression. It is useful to avoid certain values by turning them into NULL.

intermediate

How can COALESCE be used as a shortcut for a CASE statement?

Instead of writing a CASE to check for NULL and provide a default, COALESCE can directly return the first non-NULL value, simplifying the query.

intermediate

Give an example of NULLIF replacing a CASE statement.

Instead of CASE WHEN a = b THEN NULL ELSE a END, you can write NULLIF(a, b) which returns NULL if a equals b, else returns a.

beginner

Why use COALESCE and NULLIF instead of CASE statements?

They make queries shorter and easier to read by handling common NULL-related logic without verbose CASE syntax.

What will COALESCE(NULL, NULL, 'apple', 'banana') return?

A 'banana'

B 'apple'

C NULL

D Error

COALESCE returns the first non-NULL value, which is 'apple' here.

What does NULLIF(5, 5) return?

A 5

B Error

C NULL

D 0

NULLIF returns NULL if both arguments are equal.

Which SQL expression is equivalent to CASE WHEN col IS NULL THEN 'N/A' ELSE col END?

A COALESCE(col, 'N/A')

B NULLIF(col, 'N/A')

C COALESCE('N/A', col)

D NULLIF('N/A', col)

COALESCE(col, 'N/A') returns col if not NULL, else 'N/A'.

What does NULLIF('test', 'test') return?

A 'test'

B Empty string

C Error

D NULL

NULLIF returns NULL when both inputs are equal.

Why might you choose COALESCE over a CASE statement?

A COALESCE can handle multiple values more simply

B COALESCE is slower but more readable

C CASE cannot handle NULL values

D COALESCE always returns NULL

COALESCE can check multiple values in order and return the first non-NULL, simplifying queries.

Explain how COALESCE can simplify handling NULL values compared to CASE statements.

Think about how you pick the first available option in a list.

Describe a scenario where NULLIF is useful and how it acts as a shortcut for CASE.

Consider when you want to treat a certain value as missing.