COALESCE for NULL handling 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. If all values are NULL, it returns NULL.

beginner

How does COALESCE help with NULL values in SQL queries?

It allows you to replace NULL values with a default or alternative value, making results easier to read and process.

beginner

Example: What is the result of COALESCE(NULL, NULL, 'apple', 'banana')?

The result is 'apple' because it is the first non-NULL value in the list.

beginner

Can COALESCE take more than two arguments?

Yes, COALESCE can take two or more arguments and returns the first non-NULL among them.

intermediate

How is COALESCE different from ISNULL or NVL?

COALESCE is standard SQL and can take multiple arguments, while ISNULL (SQL Server) and NVL (Oracle) usually take two arguments and are vendor-specific.

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

A 'Hello'

B 'World'

C NULL

D Error

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

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

A Empty string

B NULL

C 0

D Error

COALESCE returns NULL if all arguments are NULL.

Which of these is a valid use of COALESCE?

A COALESCE(column1, column2, 'default')

B COALESCE('default')

C COALESCE()

D COALESCE(NULL)

COALESCE requires at least two arguments; option D is valid.

COALESCE is most similar to which function?

A SUM

B COUNT

C ISNULL

D AVG

COALESCE and ISNULL both handle NULL values by returning a replacement.

What does COALESCE(NULL, NULL, NULL) return?

A Error

B 0

C Empty string

D NULL

All arguments are NULL, so COALESCE returns NULL.

Explain how COALESCE helps handle NULL values in SQL queries.

Think about what happens when you have missing data.

Write a simple SQL example using COALESCE to replace NULL with 'N/A'.

Use COALESCE with two arguments.