COALESCE - SQL Programming

NULL means a value is unknown or missing. COALESCE returns the first non-NULL value from a list.

Program

Play the script to choose the fallback label used when an email address is missing.

coalesce_defaults.sql

CREATE TABLE contacts (name TEXT, email TEXT);
INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
WITH params(fallback) AS (VALUES ()) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;

CREATE TABLE contacts (name TEXT, email TEXT);
INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
WITH params(fallback) AS (VALUES ()) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;

CREATE TABLE contacts (name TEXT, email TEXT);
INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
WITH params(fallback) AS (VALUES ()) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;

NULL `NULL` represents a missing or unknown value.

COALESCE `COALESCE(email, fallback)` returns `email` unless it is NULL.

fallback A parameter CTE can provide the replacement label.