SQL COALESCE Explained: Handle NULL Values Like a Pro

Basic Example:

-- Returns 'Found it!' (first non-NULL value)
SELECT COALESCE(NULL, NULL, 'Found it!', 'Too late');

-- Returns the first available contact method
SELECT COALESCE(witness_statement, anonymous_tip, 'No leads')
FROM case_files;

Syntax:

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

Takes 2 or more arguments. Returns the first non-NULL value, or NULL if all arguments are NULL.

With 2, 3, and 4+ arguments:

-- Two arguments (most common)
SELECT COALESCE(phone, 'No phone on file') FROM suspects;

-- Three arguments (fallback chain)
SELECT COALESCE(mobile, work_phone, 'No contact')
FROM witnesses;

-- Four arguments (extended fallback)
SELECT COALESCE(email, mobile, office_phone, 'Unreachable')
FROM persons_of_interest;

-- Clean report output: no ugly NULLs
SELECT
    name,
    COALESCE(phone, 'No phone on file') AS contact_number,
    COALESCE(email, 'No email provided') AS email_address
FROM suspects;

Replace NULL with human-readable defaults for reports and dashboards.

-- Prevent NULL from breaking calculations
SELECT
    case_id,
    COALESCE(physical_evidence_count, 0)
      + COALESCE(digital_evidence_count, 0)
      + COALESCE(witness_count, 0) AS total_leads
FROM cases;

Wrap any column that might be NULL with COALESCE(column, 0) before doing arithmetic.

-- Try multiple contact methods in priority order
SELECT
    name,
    COALESCE(mobile, work_phone, home_phone, 'No contact info')
      AS best_contact
FROM witnesses;

Check mobile first. If NULL, try work phone. If NULL, try home phone. If all NULL, show a default message.

-- Handle unmatched rows from LEFT JOIN
SELECT
    cs.location,
    cs.description,
    COALESCE(s.name, 'No suspect identified') AS suspect
FROM crime_scenes cs
LEFT JOIN suspects s ON cs.suspect_id = s.id;

LEFT JOINs produce NULLs for unmatched rows. COALESCE turns those NULLs into meaningful values.

-- Push NULL priorities to the bottom
SELECT case_id, title, priority
FROM cases
ORDER BY COALESCE(priority, 999);

Without COALESCE, NULL priority rows sort unpredictably (varies by database). COALESCE(priority, 999) pushes them to the end.

Same query, four syntaxes:

-- SQL Standard (works everywhere)
SELECT COALESCE(phone, 'Unknown') FROM suspects;

-- SQL Server only
SELECT ISNULL(phone, 'Unknown') FROM suspects;

-- MySQL only
SELECT IFNULL(phone, 'Unknown') FROM suspects;

-- Oracle only
SELECT NVL(phone, 'Unknown') FROM suspects;

Same result, but only COALESCE is portable. If you switch databases, COALESCE keeps working.

-- Empty string is NOT NULL
SELECT COALESCE('', 'Fallback');
-- Returns '' (empty string), NOT 'Fallback'

-- To treat empty strings as missing:
SELECT COALESCE(NULLIF(notes, ''), 'No notes on file')
FROM case_files;

NULLIF(notes, '') converts empty strings to NULL first, then COALESCE handles the fallback.

Q: Show employees with their manager name, or 'No Manager' for top-level employees.

SELECT
    e.name AS employee,
    COALESCE(m.name, 'No Manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Classic self-join + COALESCE combo. The LEFT JOIN produces NULLs for employees without managers, and COALESCE replaces them.

Q: What's the difference between COALESCE and ISNULL?

Three key differences: COALESCE is SQL standard (works everywhere), takes 2+ arguments, and uses highest-precedence type coercion. ISNULL is SQL Server-only, takes exactly 2 arguments, and uses first-argument type coercion (which can silently truncate data).