COALESCE for NULL handling in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the COALESCE function do in SQL?

COALESCE returns the first non-NULL value from a list of expressions. It helps handle NULL values by providing a fallback value.

beginner

How does COALESCE handle multiple NULL values?

COALESCE checks each value in order and returns the first one that is not NULL. If all values are NULL, it returns NULL.

beginner

Write a COALESCE example to return 'Unknown' if a column 'name' is NULL.

SELECT COALESCE(name, 'Unknown') FROM table_name;

intermediate

Why is COALESCE preferred over CASE WHEN for NULL handling?

COALESCE is simpler and more readable for checking multiple NULL values compared to writing multiple CASE WHEN conditions.

intermediate

Can COALESCE handle different data types in its arguments?

All arguments in COALESCE must be of compatible data types or implicitly convertible to a common type; otherwise, it causes an error.

What will COALESCE(NULL, NULL, 'Hello', 'World') return?

A 'Hello'

B NULL

C 'World'

D Error

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

If all arguments to COALESCE are NULL, what is the result?

A Empty string

B Error

C 0

D NULL

COALESCE returns NULL if all arguments are NULL.

Which SQL function is best for replacing NULL values with a default?

A COUNT()

B SUM()

C COALESCE()

D AVG()

COALESCE is designed to replace NULL values with the first non-NULL value.

What happens if COALESCE arguments have incompatible data types?

A Returns first argument

B Raises an error

C Returns NULL

D Converts all to string

COALESCE requires compatible data types; otherwise, it raises an error.

Which of these is a valid COALESCE usage?

A COALESCE(name, 'No Name')

B COALESCE()

C COALESCE(5, 'text')

D COALESCE(name, 0)

COALESCE(name, 'No Name') is valid if 'name' is text or nullable text. Mixing incompatible types like number and text causes errors.

Explain how COALESCE helps handle NULL values in SQL queries.

Think about what happens when a column has missing data.

Describe a scenario where using COALESCE improves query results.

Imagine showing user names but some are missing.