Imagine your manager asks you to prepare a salary report.
Some employees have a bonus, while others have no bonus recorded.
Your manager wants to see 0 instead of NULL for missing bonuses.
You write a query, but the result still shows NULL.
What went wrong?
Let's look at the data.
employees
emp_id| name| department| salary| bonus
101 | Amit | IT | 90000| 10000
102 | Priya | IT | 70000| NULL
103 | Rahul | IT | 60000| 5000
104 | Neha | HR | 80000| NULL
105 | Sneha | HR | 85000| 15000
106 | Kiran | HR | 95000| NULL
107 | Meera | Finance | 75000| 8000
108 | Arjun | Finance | 55000| NULL
Your challenge
Display employee name, salary and bonus.
If the bonus is NULL, show 0 instead.
Which query would you choose?
Option A
SELECT name, salary,
COALESCE(bonus, 0) AS bonus
FROM employees;
Option B
SELECT name, salary,
COALESCE(0, bonus) AS bonus
FROM employees;
Option C
SELECT name, salary,
bonus + 0 AS bonus
FROM employees;
SQL Solution
Option A is correct.
SELECT name, salary,
COALESCE(bonus, 0) AS bonus
FROM employees;
Output
name| salary | bonus
Amit | 90000 | 10000
Priya| 70000 | 0
Rahul| 60000 | 5000
Neha| 80000 | 0
Sneha| 85000 | 15000
Kiran| 95000 | 0
Meera| 75000 | 8000
Arjun| 55000 | 0
Why does this work?
COALESCE checks the values from left to right and returns the first value that is not NULL.
For example:
COALESCE(10000, 0) returns 10000.
COALESCE(NULL, 0) returns 0.
Now look at Option B.
COALESCE(0, bonus)
It always returns 0 because 0 is already a valid value.
And Option C?
bonus + 0 still returns NULL when bonus is NULL.
Here is the important lesson:
NULL does not mean zero.
It means the value is missing or unknown.
COALESCE helps you show a useful value when the original value is missing. It does not change the data stored in the table.
Your turn
What will this query return?
SELECT COALESCE(NULL, NULL, 50, 100);
Will the result be NULL, 50 or 100?
Share your answer in the comments.
Want to learn more SQL with practical examples?
Top comments (0)