Strings and dates Updated November 2026

COALESCE

COALESCE(a, b, c) returns the first argument that is not NULL, so COALESCE(phone, 'unknown') shows unknown where phone is missing and COALESCE(bonus, 0) lets NULL bonuses add up as zero. It works in every SQL database; IFNULL (SQLite, MySQL), NVL (Oracle) and ISNULL (SQL Server) are two-argument variants.

Syntax

COALESCE(value1, value2, ...)

Examples

SELECT name, COALESCE(phone, 'no phone') AS phone FROM customers;

Result: name | phone Ana | 5551234 Ben | no phone

SELECT name, salary + COALESCE(bonus, 0) AS total_pay FROM employees;

Result: Total pay with NULL bonuses treated as 0

Without COALESCE, salary + NULL is NULL.

SELECT COALESCE(SUM(amount), 0) AS revenue FROM orders WHERE status = 'paid' AND order_date >= '2030-01-01';

Result: revenue 0

SUM over zero rows is NULL; COALESCE turns it into 0 for reports.

Common mistakes

Mixing types in the arguments (COALESCE(amount, 'n/a')) fails in PostgreSQL; convert to text first or use a numeric default.
Using COALESCE to treat empty strings as missing; '' is not NULL, so use NULLIF(col, '') inside: COALESCE(NULLIF(col, ''), 'default').
Writing WHERE COALESCE(status, '') = 'paid' on a big table; it defeats indexes, so prefer WHERE status = 'paid'.

Practice COALESCE

These drills check your answer on live data, free, no account needed.

No managerReturn the names of employees who have no manager.
Everyone with a departmentReturn every employee's name and department name. Employees without a department must still appear, with NULL as the department name.

Questions

What is the difference between COALESCE and IFNULL?

IFNULL(a, b) takes exactly two arguments and exists in SQLite and MySQL. COALESCE takes any number and is standard SQL, so it is the portable choice.

What is NULLIF?

NULLIF(a, b) returns NULL when a equals b, otherwise a. It is the opposite direction: NULLIF(qty, 0) prevents division by zero because x / NULL is NULL rather than an error.

Can COALESCE be used in WHERE or ORDER BY?

Yes, anywhere an expression is allowed, for example ORDER BY COALESCE(ship_date, order_date).

IS NULLNULL means unknown, so = NULL never matches. Use IS NULL and IS NOT NULL to find or exclude missing values, and see how NULL behaves in counts and sorting.
CASE WHENCASE WHEN returns different values depending on conditions. Label rows, bucket numbers into ranges, pivot with SUM(CASE ...), sort custom orders, handle NULL.
LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
SUM, AVG, MIN, MAXThe core aggregates: SUM adds values, AVG averages them, MIN and MAX find the smallest and largest. NULL handling, integer division in AVG, use with GROUP BY.