Nulls and Three-Valued Logic

COALESCE

Substitute for NULL

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.

fallback
coalesce_defaults.sql
Replay: real traced execution (multi-file project)
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 ('missing')) 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 ('none')) 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 ('unknown')) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE contacts (name TEXT, email TEXT);2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
    values this step1 rowtables

  2. contacts ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');3WITH params(fallback) AS (VALUES ('missing')) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE contacts (name TEXT, email TEXT);2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
    values this step1 rowtables

  2. contacts ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');3WITH params(fallback) AS (VALUES ('none')) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE contacts (name TEXT, email TEXT);2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');
    values this step1 rowtables

  2. contacts ← 3 rows

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

  3. result ← 3 rows

    2INSERT INTO contacts VALUES ('Ada', 'ada@example.com'), ('Lin', NULL), ('Mia', 'mia@example.com');3WITH params(fallback) AS (VALUES ('unknown')) SELECT name, COALESCE(email, (SELECT fallback FROM params)) AS email_label FROM contacts ORDER BY name;
    values this step3 rowsresult
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.