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.

Practice

1. What does the COALESCE function do in PostgreSQL?

easy

A. Returns the first non-NULL value from the list of arguments

B. Returns the last value in the list regardless of NULL

C. Counts the number of NULL values in a column

D. Deletes rows with NULL values

Solution

1. Step 1: Understand COALESCE purpose

   COALESCE checks each argument in order and returns the first one that is not NULL.

2. Step 2: Compare options with definition

   Only Returns the first non-NULL value from the list of arguments correctly describes this behavior; others describe unrelated functions.

3. Final Answer:

   Returns the first non-NULL value from the list of arguments -> Option A

4. Quick Check:

   COALESCE = first non-NULL value [OK]

Hint: COALESCE picks first non-NULL value in order [OK]

Common Mistakes:

• Thinking COALESCE counts NULLs
• Assuming it returns last value
• Confusing with DELETE or COUNT functions

2. Which of the following is the correct syntax to use COALESCE in PostgreSQL?

easy

A. COALESCE(value1; value2; ...; valueN)

B. COALESCE[value1, value2, ..., valueN]

C. COALESCE(value1, value2, ..., valueN)

D. COALESCE{value1, value2, ..., valueN}

Solution

1. Step 1: Recall function call syntax in SQL

   Functions use parentheses with comma-separated arguments.

2. Step 2: Match options to syntax rules

   Only COALESCE(value1, value2, ..., valueN) uses parentheses and commas correctly; others use invalid separators or brackets.

3. Final Answer:

   COALESCE(value1, value2, ..., valueN) -> Option C

4. Quick Check:

   Function syntax = parentheses + commas [OK]

Hint: Use parentheses and commas for SQL functions [OK]

Common Mistakes:

• Using semicolons instead of commas
• Using square or curly brackets
• Omitting parentheses

3. Given the table users with columns id, nickname, and fullname, what will this query return?

SELECT id, COALESCE(nickname, fullname, 'Anonymous') AS display_name FROM users;

Assuming a row has nickname = NULL and fullname = 'John Doe'.

medium

A. NULL

B. 'John Doe'

C. 'Anonymous'

D. nickname value

Solution

1. Step 1: Evaluate COALESCE arguments in order

   First argument is nickname which is NULL, so move to next.

2. Step 2: Check fullname value

   fullname is 'John Doe', which is not NULL, so COALESCE returns 'John Doe'.

3. Final Answer:

   'John Doe' -> Option B

4. Quick Check:

   First non-NULL = 'John Doe' [OK]

Hint: COALESCE returns first non-NULL value in order [OK]

Common Mistakes:

• Assuming NULL is returned if first value is NULL
• Ignoring order of arguments
• Choosing default 'Anonymous' incorrectly

4. Identify the error in this query:

SELECT id, COALESCE nickname, fullname, 'Guest' AS display_name FROM users;

medium

A. AS keyword misplaced

B. Wrong keyword instead of COALESCE

C. No error, query is correct

D. Missing parentheses around COALESCE arguments

Solution

1. Step 1: Check COALESCE function syntax

   COALESCE requires parentheses around its arguments.

2. Step 2: Analyze given query

   Query uses COALESCE without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around COALESCE arguments -> Option D

4. Quick Check:

   Functions need parentheses [OK]

Hint: Always use parentheses with COALESCE arguments [OK]

Common Mistakes:

• Omitting parentheses for functions
• Misplacing AS keyword
• Confusing COALESCE with other functions

5. You want to display a user's preferred contact method from columns email, phone, and address. If all are NULL, show 'No contact info'. Which query correctly uses COALESCE to achieve this?

hard

A. SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users;

B. SELECT id, COALESCE(email, phone, address) OR 'No contact info' AS contact FROM users;

C. SELECT id, COALESCE(email, phone, address) ?? 'No contact info' AS contact FROM users;

D. SELECT id, COALESCE(email, phone, address, NULL) AS contact FROM users;

Solution

1. Step 1: Understand COALESCE usage for defaults

   COALESCE returns first non-NULL; adding 'No contact info' as last argument provides default.

2. Step 2: Evaluate each option's correctness

   SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users; correctly lists all contact columns plus default string. Options B and C use invalid operators, D ends with NULL, no default.

3. Final Answer:

   SELECT id, COALESCE(email, phone, address, 'No contact info') AS contact FROM users; -> Option A

4. Quick Check:

   Default last in COALESCE list [OK]

Hint: Put default last inside COALESCE parentheses [OK]

Common Mistakes:

• Using OR or ?? instead of COALESCE
• Not providing a default value
• Ending COALESCE with NULL instead of default