Post

COALESCE — First Non-NULL Value

Posted
By Rahul Chandna
9 min read
COALESCE — First Non-NULL Value

title: “COALESCE (Advanced NULL Handling)” part: 2 topic_number: 5 slug: “coalesce” difficulty: “Intermediate” prerequisites: “null-handling” —

COALESCE — First Non-NULL Value

What Is It?

COALESCE() returns the first non-NULL value from a list of arguments. It’s a more powerful version of IFNULL that can check multiple columns or expressions in one go.

Real-world analogy: Like a series of backup plans — “Try option A; if that’s not available, try B; if not, try C…” until you find one that works.

When you’d use it:

  • Provide default values for NULL columns
  • Choose between multiple optional data sources
  • Create fallback chains
  • Clean up reports with meaningful defaults

Syntax Breakdown

1

COALESCE(value1, value2, value3, ..., defaultValue)

How it works:

  • Evaluates arguments left to right
  • Returns the first non-NULL value
  • If all are NULL, returns NULL (unless you provide a final default)

vs IFNULL:

  • IFNULL(col, default) — checks only ONE column
  • COALESCE(col1, col2, col3, default) — checks MULTIPLE values

Mental Model — Left-to-Right Scan

Think of COALESCE as scanning a list from left to right and stopping at the first real value:

1
2
3
4
5
6
7
8

COALESCE(mobile_phone, office_phone, 'No phone')
           ↓
   Is mobile_phone NULL?
   ├── NO  → return mobile_phone   ← stops here
   └── YES → check next...
              Is office_phone NULL?
              ├── NO  → return office_phone
              └── YES → return 'No phone'

Quick decision: use IFNULL when you have one column and one fallback. Use COALESCE when you have two or more columns to try in order.

Basic Examples

Simple Default Value

1
2
3
4
5
6
7

SELECT 
    first_name,
    last_name,
    email,
    COALESCE(email, '[email protected]') AS email_with_default
FROM employees
LIMIT 5;

Expected output:

first_name last_name email email_with_default
Alice Johnson NULL [email protected]
Bob Smith [email protected] [email protected]
Carol Williams NULL [email protected]
David Brown [email protected] [email protected]
Eve Davis [email protected] [email protected]

Replaces NULL emails with a default.

Multiple Fallbacks

Show each employee’s “best available” contact — preferring a specific email format, falling back to manager info, then a default:

1
2
3
4
5
6
7
8
9
10
11

-- Using columns that already exist in our company DB
SELECT 
    e.first_name,
    e.last_name,
    COALESCE(
        e.email,
        CONCAT('reports-to-emp-', e.manager_id, '@company.com'),
        '[email protected]'
    ) AS best_contact
FROM employees e
LIMIT 8;

Expected output:

first_name last_name best_contact
Alice Johnson [email protected]
Bob Smith [email protected]
Paul Garcia [email protected]

What happened: Checks email first. If NULL, builds a fallback from manager_id. If manager_id is also NULL, uses the final default.

Going Deeper

COALESCE with Calculations

Calculate commission (prefer actual commission, fallback to salary * 5%):

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Add commission column for demo
ALTER TABLE employees ADD COLUMN commission DECIMAL(10, 2);

UPDATE employees SET commission = 5000 WHERE employee_id IN (3, 5, 11);

SELECT 
    first_name,
    last_name,
    salary,
    commission,
    COALESCE(commission, salary * 0.05) AS effective_commission
FROM employees
ORDER BY salary DESC
LIMIT 8;

Expected output:

first_name last_name salary commission effective_commission
Sam Clark 95000.00 NULL 4750.00
Frank Miller 93000.00 NULL 4650.00
Henry Moore 92000.00 NULL 4600.00
Paul Garcia 87000.00 NULL 4350.00
Bob Smith 82000.00 NULL 4100.00
Carol Williams 78000.00 5000.00 5000.00
Alice Johnson 75000.00 NULL 3750.00
Ivy Anderson 74000.00 5000.00 5000.00

Uses actual commission if available, otherwise calculates 5%.

Complex Data Merging

Show best available address from multiple sources:

1
2
3
4
5
6
7
8
9
10
11

SELECT 
    employee_id,
    first_name,
    COALESCE(
        NULLIF(preferred_address, ''),
        NULLIF(mailing_address, ''),
        NULLIF(home_address, ''),
        'Address on file'
    ) AS primary_address
FROM employees
LIMIT 5;

Key technique: NULLIF(col, '') treats empty strings as NULL, so COALESCE skips them too!

Conditional Defaults with CASE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT 
    first_name,
    last_name,
    department_id,
    salary,
    COALESCE(
        commission,
        CASE
            WHEN department_id = 3 THEN salary * 0.10  -- Sales gets 10%
            WHEN department_id = 2 THEN salary * 0.07  -- Marketing gets 7%
            ELSE salary * 0.05                         -- Others get 5%
        END
    ) AS total_commission
FROM employees
ORDER BY total_commission DESC
LIMIT 10;

Expected output:

first_name last_name department_id salary total_commission
Frank Miller 2 93000.00 6510.00
Bob Smith 3 82000.00 8200.00
Carol Williams 3 78000.00 5000.00
… … … … …

Uses actual commission if exists, otherwise calculates based on department.

Pause and Predict: What does COALESCE(NULL, NULL, NULL) return?

Answer `NULL` If ALL arguments are NULL, COALESCE returns NULL. Always provide a non-NULL default at the end if you want to avoid this: `COALESCE(val1, val2, 'default')`.

Watch Out — Common Mistakes

Mistake #1: Not Providing a Final Default

1
2
3

--  CAN STILL RETURN NULL
SELECT COALESCE(email, phone) AS contact
FROM employees;

Problem: If both email AND phone are NULL, result is NULL.

Fix: Add a non-NULL default:

1
2
3

-- • ALWAYS HAS A VALUE
SELECT COALESCE(email, phone, 'No contact info') AS contact
FROM employees;

Mistake #2: Type Mismatches

1
2

--  TYPE ERROR (mixing numbers and strings)
SELECT COALESCE(salary, 'Unknown');

Error: MySQL tries to convert types, leading to unexpected results (salary might be converted to string ‘0’).

Fix: Keep types consistent:

1
2
3
4
5

-- • CORRECT (all strings)
SELECT COALESCE(CAST(salary AS CHAR), 'Unknown');

-- Or (all numbers)
SELECT COALESCE(salary, 0);

Mistake #3: Forgetting Empty Strings Aren’t NULL

1
2
3

--  MISSES EMPTY STRINGS
SELECT COALESCE(email, '[email protected]')
FROM employees;

Problem: If email is '' (empty string), COALESCE sees it as a value (not NULL) and uses it.

Fix: Use NULLIF to treat empty strings as NULL:

1
2
3

-- • CORRECT
SELECT COALESCE(NULLIF(email, ''), '[email protected]')
FROM employees;

Edge Case Spotlight

COALESCE vs CASE WHEN

These are equivalent:

1
2
3
4
5
6
7
8
9
10

-- Using COALESCE
SELECT COALESCE(email, phone, 'No contact') AS contact;

-- Using CASE WHEN
SELECT 
    CASE
        WHEN email IS NOT NULL THEN email
        WHEN phone IS NOT NULL THEN phone
        ELSE 'No contact'
    END AS contact;

COALESCE is shorter and clearer for this pattern!

Performance Consideration

1
2
3
4
5
6
7
8

--  INEFFICIENT (recalculates expression)
SELECT 
    COALESCE(
        (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id),
        (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id),
        0
    )
FROM employees e;

Problem: Subquery runs multiple times unnecessarily.

Fix: Calculate once, then use COALESCE:

1
2
3
4
5
6
7
8
9

-- • BETTER
SELECT 
    COALESCE(dept_avg.avg_sal, 0) AS avg_salary
FROM employees e
LEFT JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id;

Try This

Exercise 1 (Guided)

Create a “display name” for employees using this priority:

  1. If they have a nickname, use it
  2. Otherwise use first_name
  3. If both are NULL, use ‘Employee #’ + employee_id

Add a nickname column first: ALTER TABLE employees ADD COLUMN nickname VARCHAR(50); Then set a few: UPDATE employees SET nickname = 'Ali' WHERE employee_id = 1;

Hint COALESCE(nickname, first_name, CONCAT('Employee #', employee_id))

Exercise 2 (Independent)

Show all employees with their “best contact method”: check email, then mobile_phone, then office_phone, then show ‘URGENT: Update contact info’. Also show which method was used (e.g., ‘Email’, ‘Mobile’, ‘Office’, ‘None’).

Hint Use COALESCE for the contact value, and CASE WHEN to determine which method was used.

Exercise 3 (Challenge)

Calculate “total compensation” for each employee:

  • Start with salary
  • Add commission if available
  • If no commission but department is Sales (dept_id = 3), add 10% of salary
  • Add a “retention bonus” of $5000 if hire_date is before 2020
  • Show first_name, last_name, salary, and total_compensation
Hint Combine COALESCE with CASE WHEN and date comparisons. Total = salary + COALESCE(commission, ...) + CASE...

Answer Key

Exercise 1 Answer ```sql -- Setup ALTER TABLE employees ADD COLUMN nickname VARCHAR(50); UPDATE employees SET nickname = 'Ali' WHERE employee_id = 1; UPDATE employees SET nickname = 'Bobby' WHERE employee_id = 2; UPDATE employees SET nickname = 'Dave' WHERE employee_id = 4; -- Solution SELECT employee_id, first_name, nickname, COALESCE( nickname, first_name, CONCAT('Employee #', employee_id) ) AS display_name FROM employees ORDER BY employee_id LIMIT 10; ``` **Expected output:** | employee_id | first_name | nickname | display_name | |-------------|------------|----------|--------------| | 1 | Alice | Ali | Ali | | 2 | Bob | Bobby | Bobby | | 3 | Carol | NULL | Carol | | 4 | David | Dave | Dave | | 5 | Eve | NULL | Eve | | ... | ... | ... | ... | Uses nickname if available, falls back to first_name.
Exercise 2 Answer ```sql SELECT first_name, last_name, COALESCE( email, mobile_phone, office_phone, 'URGENT: Update contact info' ) AS best_contact, CASE WHEN email IS NOT NULL THEN 'Email' WHEN mobile_phone IS NOT NULL THEN 'Mobile' WHEN office_phone IS NOT NULL THEN 'Office' ELSE 'None' END AS contact_method FROM employees ORDER BY last_name; ``` **Expected output:** | first_name | last_name | best_contact | contact_method | |------------|-----------|--------------|----------------| | Jack | Anderson | 555-1234 | Mobile | | David | Brown | [email protected] | Email | | Sam | Clark | URGENT: Update contact info | None | | ... | ... | ... | ... | Shows best contact and identifies the source.
Exercise 3 Answer ```sql SELECT first_name, last_name, salary, salary + COALESCE( commission, CASE WHEN department_id = 3 THEN salary * 0.10 ELSE 0 END ) + CASE WHEN hire_date < '2020-01-01' THEN 5000 ELSE 0 END AS total_compensation FROM employees ORDER BY total_compensation DESC LIMIT 10; ``` **Expected output:** | first_name | last_name | salary | total_compensation | |------------|-----------|---------|-------------------| | Sam | Clark | 95000.00 | 100000.00 | | Frank | Miller | 93000.00 | 103650.00 | | Henry | Moore | 92000.00 | 97000.00 | | Bob | Smith | 82000.00 | 95200.00 | | Carol | Williams | 78000.00 | 90800.00 | | ... | ... | ... | ... | Complete compensation calculation with all bonuses. **Breakdown:** - Base salary - + Commission (or 10% for Sales dept) - + $5K retention bonus if hired pre-2020

Quick Recap

• COALESCE returns first non-NULL value from a list
• More powerful than IFNULL (checks multiple values)
• Always provide a final non-NULL default to avoid NULL results
• All arguments must be compatible data types
• Use NULLIF to treat empty strings as NULL
• Shorter and clearer than equivalent CASE WHEN for NULL checks
• Common uses: default values, fallback chains, data merging

Up Next

Next topic: Nested Subqueries (Subqueries in SELECT/WHERE) → part2_06_nested_subqueries.md

Type ‘next’ when ready to continue!

Graduate Training, MySQL
Graduate Training
This post is licensed under CC BY 4.0 by the author.