COALESCE for NULL handling in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of COALESCE with multiple NULLs

Given the table employees with columns id, name, and bonus where some bonus values are NULL, what is the output of this query?

SELECT id, COALESCE(bonus, 1000) AS bonus_amount FROM employees ORDER BY id;

PostgreSQL

CREATE TABLE employees (id INT, name TEXT, bonus INT);
INSERT INTO employees VALUES (1, 'Alice', NULL), (2, 'Bob', 500), (3, 'Charlie', NULL);

A [{"id":1,"bonus_amount":1000},{"id":2,"bonus_amount":500},{"id":3,"bonus_amount":1000}]

B [{"id":1,"bonus_amount":NULL},{"id":2,"bonus_amount":500},{"id":3,"bonus_amount":NULL}]

C [{"id":1,"bonus_amount":0},{"id":2,"bonus_amount":500},{"id":3,"bonus_amount":0}]

D [{"id":1,"bonus_amount":1000},{"id":2,"bonus_amount":NULL},{"id":3,"bonus_amount":1000}]

💡 Hint

COALESCE returns the first non-NULL value from its arguments.

The COALESCE function replaces NULL bonus values with 1000. Bob's bonus is 500, so it stays the same.

🧠 Conceptual

intermediate

Understanding COALESCE with multiple arguments

What will be the result of this expression in PostgreSQL?

SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null');

A 'first non-null'

B NULL

C Syntax error

D 'second non-null'

💡 Hint

COALESCE returns the first argument that is not NULL.

COALESCE checks each argument in order and returns the first one that is not NULL, which is 'first non-null'.

📝 Syntax

advanced

Identify the syntax error in COALESCE usage

Which option contains a syntax error when using COALESCE in PostgreSQL?

A SELECT COALESCE(5, NULL, 10);

B SELECT COALESCE(NULL 5, 10);

C SELECT COALESCE(NULL, 5, 10);

D SELECT COALESCE(NULL, NULL, 10);

💡 Hint

Check for missing commas between arguments.

Option B is missing a comma between NULL and 5, causing a syntax error.

❓ optimization

advanced

Optimizing COALESCE with subqueries

Consider this query:

SELECT id, COALESCE((SELECT bonus FROM bonuses WHERE bonuses.emp_id = employees.id), 0) AS bonus_amount FROM employees;

Which option best improves performance without changing the output?

A Use COALESCE with a CASE statement:<br>SELECT id, COALESCE(CASE WHEN bonus IS NULL THEN 0 ELSE bonus END, 0) AS bonus_amount FROM employees;

B Replace COALESCE with IFNULL:<br>SELECT id, IFNULL((SELECT bonus FROM bonuses WHERE bonuses.emp_id = employees.id), 0) AS bonus_amount FROM employees;

C Use LEFT JOIN instead of subquery:<br>SELECT e.id, COALESCE(b.bonus, 0) AS bonus_amount FROM employees e LEFT JOIN bonuses b ON b.emp_id = e.id;

D Add WHERE bonus IS NOT NULL in subquery:<br>SELECT id, COALESCE((SELECT bonus FROM bonuses WHERE bonuses.emp_id = employees.id AND bonus IS NOT NULL), 0) AS bonus_amount FROM employees;

💡 Hint

Replacing subqueries with joins often improves query speed.

LEFT JOIN avoids running a subquery for each row, improving performance while keeping the same results.

🔧 Debug

expert

Debugging unexpected NULL results with COALESCE

A developer runs this query:

SELECT id, COALESCE(bonus, 0) FROM employees;

But some rows still show NULL for the bonus column. Which option explains the most likely cause?

A The employees table has no rows, so the query returns NULL.

B The COALESCE function is case-sensitive and should be written as coalesce.

C The query is missing an alias for the COALESCE column, causing NULL display.

D The bonus column is of type JSON and contains JSON null, which COALESCE does not treat as SQL NULL.

💡 Hint

COALESCE only replaces SQL NULL, not other types of null-like values.

In PostgreSQL, JSON null values are not SQL NULL and COALESCE does not replace them, so they appear as NULL in output.