COALESCE for NULL handling in SQL - Step-by-Step Execution

Concept Flow - COALESCE for NULL handling

Start with list of expressions

↓

Check first expression

↓

Return it

↓

Repeat until non-NULL found or list ends

↓

If all NULL, return NULL

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

Execution Sample

SQL

SELECT COALESCE(NULL, NULL, 'apple', 'banana');

Returns the first non-NULL value from the list, which is 'apple'.

Execution Table

Step	Expression Checked	Is NULL?	Action	Value Returned
1	NULL	Yes	Check next expression	NULL
2	NULL	Yes	Check next expression	NULL
3	'apple'	No	Return this value	'apple'
4	'banana'	Not checked	Stop checking	'apple'

💡 Stopped at step 3 because 'apple' is the first non-NULL value.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Current Expression	NULL	NULL	'apple'	'apple'	'apple'
Value Returned	NULL	NULL	NULL	'apple'	'apple'

Key Moments - 2 Insights

Why does COALESCE return 'apple' and not 'banana'?

Because COALESCE returns the first non-NULL value it finds. According to the execution_table, at step 3 it finds 'apple' which is not NULL, so it stops and returns it without checking 'banana'.

What happens if all expressions are NULL?

COALESCE will check all expressions and find all are NULL, so it returns NULL. This is shown by the logic in the concept_flow where if no non-NULL is found, NULL is returned.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value returned after step 2?

A 'apple'

B NULL

C 'banana'

D Error

💡 Hint

Check the 'Value Returned' column at step 2 in the execution_table.

At which step does COALESCE stop checking expressions?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in the execution_table to see when it returns a value.

If the first expression was 'orange' instead of NULL, what would COALESCE return?

A 'orange'

B 'banana'

C 'apple'

D NULL

💡 Hint

COALESCE returns the first non-NULL expression, so check the first expression value.

Concept Snapshot

COALESCE(expression1, expression2, ...)
Returns the first non-NULL expression in the list.
If all expressions are NULL, returns NULL.
Useful for replacing NULLs with default values.
Evaluates expressions left to right and stops at first non-NULL.

Full Transcript

COALESCE is a SQL function that helps handle NULL values by returning the first non-NULL value from a list of expressions. It checks each expression in order. If the expression is NULL, it moves to the next one. When it finds a non-NULL value, it returns that immediately and stops checking further. If all expressions are NULL, COALESCE returns NULL. For example, COALESCE(NULL, NULL, 'apple', 'banana') returns 'apple' because it is the first non-NULL value. This function is useful when you want to provide a fallback or default value instead of NULL in your query results.

Practice

1. What does the SQL function COALESCE do when used in a query?

easy

A. Converts all values to NULL

B. Counts the number of NULL values in a column

C. Deletes rows with NULL values

D. Returns the first non-NULL value from the given list of expressions

Solution

1. Step 1: Understand the purpose of COALESCE

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

2. Step 2: Compare with other options

   Counting NULLs, deleting rows, or converting values to NULL are not what COALESCE does.

3. Final Answer:

   Returns the first non-NULL value from the given list of expressions -> Option D

4. Quick Check:

   COALESCE = first non-NULL value [OK]

Hint: COALESCE picks the first value that is not NULL [OK]

Common Mistakes:

• Thinking COALESCE counts NULLs
• Confusing COALESCE with DELETE
• Assuming COALESCE converts values to NULL

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

easy

A. COALESCE(column1, column2, 'default')

B. COALESCE(column1; column2; 'default')

C. COALESCE(column1 column2 'default')

D. COALESCE[column1, column2, 'default']

Solution

1. Step 1: Recall COALESCE syntax

   COALESCE uses parentheses and commas to separate arguments: COALESCE(value1, value2, ...).

2. Step 2: Identify correct punctuation

   Only COALESCE(column1, column2, 'default') uses commas and parentheses correctly; others use semicolons, spaces, or brackets incorrectly.

3. Final Answer:

   COALESCE(column1, column2, 'default') -> Option A

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for COALESCE arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Omitting commas between arguments
• Using square brackets instead of parentheses

3. Given the table Employees with columns FirstName, MiddleName, and LastName, what will this query return?

SELECT FirstName, COALESCE(MiddleName, 'N/A') AS Middle, LastName FROM Employees;

Assuming one row has FirstName='John', MiddleName=NULL, LastName='Doe'.

medium

A. John | N/A | Doe

B. John | NULL | Doe

C. John | '' | Doe

D. John | MiddleName | Doe

Solution

1. Step 1: Understand COALESCE behavior on NULL

   COALESCE returns the first non-NULL value. Since MiddleName is NULL, it returns 'N/A'.

2. Step 2: Apply to the given row

   FirstName is 'John', MiddleName replaced by 'N/A', LastName is 'Doe'.

3. Final Answer:

   John | N/A | Doe -> Option A

4. Quick Check:

   NULL replaced by 'N/A' using COALESCE [OK]

Hint: COALESCE replaces NULL with first non-NULL value [OK]

Common Mistakes:

• Expecting NULL instead of replacement
• Confusing empty string with NULL
• Thinking COALESCE returns column name

4. Identify the error in this SQL query:

SELECT COALESCE(FirstName MiddleName, 'Unknown') FROM Employees;

medium

A. COALESCE cannot have string literals

B. Missing comma between FirstName and MiddleName

C. COALESCE requires only one argument

D. Missing FROM clause

Solution

1. Step 1: Check COALESCE argument syntax

   Arguments must be separated by commas. Here, FirstName and MiddleName are missing a comma.

2. Step 2: Verify other parts

   String literals are allowed, multiple arguments allowed, and FROM clause is present.

3. Final Answer:

   Missing comma between FirstName and MiddleName -> Option B

4. Quick Check:

   Arguments need commas in COALESCE [OK]

Hint: Separate COALESCE arguments with commas [OK]

Common Mistakes:

• Forgetting commas between arguments
• Thinking COALESCE only takes one argument
• Ignoring syntax errors in argument list

5. You have a table Orders with columns OrderID, Discount, and SpecialDiscount. Some rows have NULL in both discount columns. You want to write a query that returns OrderID and the first available discount value, or 0 if both are NULL. Which query correctly uses COALESCE to achieve this?

hard

A. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders;

B. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders;

C. SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders;

D. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders;

Solution

1. Step 1: Understand the requirement

   Return the first non-NULL discount from Discount or SpecialDiscount, or 0 if both are NULL.

2. Step 2: Analyze each option

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; uses COALESCE with three arguments correctly. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums discounts before COALESCE, which is wrong if both are NULL. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums COALESCE results, which adds discounts instead of picking first non-NULL. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders; misses the default 0.

3. Final Answer:

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; -> Option C

4. Quick Check:

   COALESCE with fallback 0 picks first non-NULL or 0 [OK]

Hint: Use COALESCE with all options and default last [OK]

Common Mistakes:

• Adding discounts instead of picking first non-NULL
• Forgetting default fallback value
• Using COALESCE incorrectly with expressions