NULL Handling and COALESCE

NULL is how SQL marks a value that is missing or unknown. It is not zero and not the empty string ''; it is the absence of any value, and it follows its own rules in comparisons, arithmetic and aggregates. By the end you can find missing values, replace them with a fallback, and turn a fake placeholder into a real NULL.

Comparing with NULL: use IS NULL

A contacts table where some people have no phone on record:

CREATE TABLE contacts (name TEXT, mobile TEXT, landline TEXT);
INSERT INTO contacts VALUES
    ('Ines',  '555-0101', '555-0199'),
    ('Tomas', NULL,       '555-0142'),
    ('Wren',  NULL,       NULL);

A WHERE condition can come out TRUE, FALSE or NULL (unknown), and only TRUE keeps a row. Any comparison against NULL -- =, <>, <, > -- is unknown, so:

SELECT name FROM contacts WHERE mobile = NULL;   -- prints nothing, no error
SELECT name FROM contacts WHERE mobile IS NULL;  -- the right test

Tomas
Wren

IS NULL and IS NOT NULL are the only tests that answer yes or no about missingness. The first query is the classic silent bug: it runs, and it returns zero rows.

NULL spreads through expressions

Almost every operator given a NULL returns NULL:

SELECT NULL + 5, 'tel: ' || NULL, NULL = NULL;

||

Three empty columns: this runner prints NULL as nothing between the | separators. NULL = NULL is not true -- two unknowns are not known to be equal.

COALESCE: the first value that is not NULL

COALESCE(a, b, c, ...) reads its arguments left to right and returns the first one that is not NULL. The order you write is the priority order:

SELECT name, COALESCE(mobile, landline, 'no phone') FROM contacts;

Ines|555-0101
Tomas|555-0142
Wren|no phone

Ines has a mobile, so her landline is never looked at. Tomas falls through to his landline; Wren falls all the way to the constant. Swap the first two arguments and Ines prints 555-0199 instead. IFNULL(x, y) is SQLite's two-argument version.

COALESCE only skips NULL. An empty string '' is a real value, so COALESCE('', 'fallback') returns '' and prints a blank.

NULLIF: turning a placeholder into NULL

Data often uses a fake value for "missing": -999 from a broken sensor, '' from a form left empty. NULLIF(x, y) returns NULL when x equals y, and x otherwise:

CREATE TABLE readings (sensor TEXT, temp INTEGER);
INSERT INTO readings VALUES ('roof', 18), ('roof', -999), ('roof', 21);

SELECT AVG(temp) FROM readings;
SELECT AVG(NULLIF(temp, -999)), COUNT(NULLIF(temp, -999)) FROM readings;

-320.0
19.5|2

The placeholder dragged the first average to -320.0. Once it is NULL, AVG and COUNT(column) skip it, as the aggregates lesson showed. Because NULLIF produces a NULL, you can hand its result to COALESCE, and the placeholder then falls through to the next argument like any other missing value.

Common mistakes

• Writing = NULL in WHERE. Every row's test is unknown, so the query prints no lines at all.
• Forgetting the empty string. COALESCE keeps '', so a row whose value is blank prints as 4| with nothing after the bar.
• Arguments in the wrong order. COALESCE stops at the first non-NULL argument, so putting the broader fallback first prints 1|web where the person's name belonged.
• No final constant. If every argument can be NULL, a row prints 6|. End with a literal to guarantee a value.

Your exercise

The tickets table is filled in the editor, and the starter prints every ticket's raw assignee. Change the query so it lists only the open tickets, each with the owner the rules in the exercise describe. The mistake that costs the most is treating a blank assignee as a person.