IntermediateSQL · 31 min · 13 challenges

NULL Values: Intermediate

An analyst at Uber pulls the average tip per ride for a review and gets 38 cents. Riders who closed the app before the tip screen were saved with a tip of 0, and a join to the driver table dropped every ride whose driver record failed to load. This lesson separates missing from zero, keeps rows through joins, and moves values into and out of NULL on purpose.

Sentinel values and fake NULLs

Daily Life

Interviews

What a sentinel is

A sentinel is an ordinary value a system wrote in place of a missing one. The column was declared NOT NULL, a form refused to submit without the field, or a CSV export had no way to write NULL. The table now holds a number or a string that looks like data and means 'we never knew'.

Every aggregate from the last lesson treats a sentinel as real. NULL is skipped by COUNT(age), AVG(age) and MIN(age); a 0 is included by all 3. A column of fake zeros gives a lower average, a wrong minimum and a count that claims more knowledge than the table holds.

Column	Sentinel seen in practice	How it got there
age (INTEGER)	0 or -1	required form field, column declared NOT NULL
price (DECIMAL)	0.00 or 9999.99	placeholder until the pricing team filled it in
signup_date (TIMESTAMP)	1900-01-01 or 1970-01-01	default of the source system when no date was known
country (VARCHAR)	'N/A', 'unknown', 'none', ''	dropdown default, or a required field typed around
middle_name (VARCHAR)	'' or the text 'NULL'	CSV export wrote the word NULL for a missing value
quantity (INTEGER)	-1 or 999999	out-of-range code meaning 'not applicable'

The damage a fake zero does

The legacy_users table holds 5 users. A signup form that required the age field, and let people pass it with the default, wrote 0 for 2 of them. Run the average over every row, then again with the zeros filtered out.

	SELECT
	'every row' AS scope,
	COUNT(*) AS n,
	AVG(age) AS avg_age
	FROM legacy_users

	UNION ALL

	SELECT
	'age above 0' AS scope,
	COUNT(*) AS n,
	AVG(age) AS avg_age
	FROM legacy_users
	WHERE age > 0

Result

scope	n	avg_age
every row	5	21.2
age above 0	3	35.333333333333336

2 rows

The first row says the average user is 21.2; the second says 35.3. AVG folded both zeros into (34 + 0 + 27 + 0 + 45) / 5, and the 14-year gap comes entirely from values that were never ages. COUNT(age) would report 5 ages known, when only 3 are.

Profiling a column to find its sentinels

Sentinels are found by profiling: GROUP BY the column, count each group, and sort the biggest first. Real values spread across a range; a sentinel piles up in one group, often at a value the column could never hold. The shipments table records parcel weights from a warehouse scanner.

	SELECT
	weight_kg,
	COUNT(*) AS n
	FROM shipments
	GROUP BY weight_kg
	ORDER BY n DESC, weight_kg

Result

weight_kg	n
-1	4
2.5	2
4	1
7.5	1
9	1
12	1

6 rows

Of the 10 shipments, 4 weigh -1 kilograms. No parcel has negative mass, and no real weight repeats more than twice, so -1 is a code for 'weight not captured'. AVG(weight_kg) over this table returns 3.35, while the 6 weighed parcels average 6.25.

Multiple Choice

A profile of discount_pct across 50,000 orders returns these counts: 0 has 31,200 rows, 5 has 8,100, 10 has 7,900, 15 has 2,750 and 100 has 50. Which value most needs a second look before you average the column?

Strings that pretend to be NULL

Text columns hide missing data behind placeholders: 'N/A', 'unknown', 'none', 'TBD', an empty string, or the 4-character word 'NULL' that a CSV export wrote when it met a real NULL. Every one of them is a value. carrier_code IS NULL is FALSE for each of them, and COUNT(carrier_code) counts each of them. The shipments table also carries a carrier code: 1 shipment has a true NULL there, and 3 others carry 'N/A'.

	SELECT
	carrier_code,
	COUNT(*) AS n,
	COUNT(carrier_code) AS counted_as_present
	FROM shipments
	GROUP BY carrier_code
	ORDER BY n DESC, carrier_code

Result

carrier_code	n	counted_as_present
FDX	3	3
N/A	3	3
NWX	3	3
NULL	1	0

4 rows

The true NULL on S-10 behaves as the last lesson described: COUNT(*) sees the row and COUNT(carrier_code) skips it, so its group reads 1 and 0. The 'N/A' group gets no such treatment: 'N/A' is a 3-character string, as present as 'NWX'. A report that counts 'shipments with a known carrier' as COUNT(carrier_code) says 9 when the correct answer is 6.

Signs that a value is a sentinel

▸It cannot happen in the real world: a weight of -1, an age of 0, a price of 0.00 on a product that is for sale
▸It repeats far more than its neighbours in a GROUP BY count
▸It sits at the edge of its type or the calendar: 9999, -1, 1900-01-01, 1970-01-01
▸It reads like a placeholder: 'N/A', 'unknown', 'none', 'TBD', '' or the text 'NULL'
▸The source schema declares the column NOT NULL, so something had to be written when nothing was known

NULLs in INNER and LEFT JOINs

Daily Life

Interviews

How a join pairs rows

A join reads 2 tables and pairs their rows. FROM orders o INNER JOIN customers c names the tables o and c for the rest of the query, and the ON clause gives the condition a pair has to satisfy. o.customer_id = c.customer_id is TRUE when both sides hold the same value, FALSE when they differ, and NULL when either side is NULL, because a comparison with NULL has no answer. The join keeps pairs whose condition is TRUE and nothing else. A NULL key therefore pairs with no row at all, including a NULL key on the other side: NULL = NULL is NULL, never TRUE.

An INNER JOIN returns the pairs and nothing more. A LEFT JOIN returns the pairs, then adds back every row of the left table that found no partner, with the right table's columns filled with NULL for those rows.

INNER JOIN drops the row

1SELECT
2  o.order_id,
3  o.customer_id,
4  c.name
5FROM orders AS o
6INNER JOIN customers AS c
7  ON o.customer_id = c.customer_id

orders4 rows

order_id

customer_id

ORD-1

C-101

ORD-2

C-102

ORD-3

NULL

ORD-4

C-103

customers4 rows

customer_id

name

C-101

Alice

C-102

Bob

NULL

Guest

C-104

Diana

Preparing the animation

The 4 orders meet 4 customers and 2 rows come out. ORD-1 and ORD-2 find Alice and Bob. The NULL customer_id on ORD-3 makes every comparison NULL, so it pairs with nobody, including the Guest customer whose key is also NULL. ORD-4 carries C-103, a key no customer owns, so every comparison is FALSE. The 2 result rows show no trace of either loss.

Multiple Choice

The orders table has 100 rows; 10 have a NULL customer_id and each of the other 90 holds a key that appears exactly once in customers. The customers table has 50 rows, 1 of them with a NULL customer_id. How many rows does this query return?

	SELECT
	COUNT(*)
	FROM orders AS o
	INNER JOIN customers AS c
	ON o.customer_id = c.customer_id

LEFT JOIN keeps the row and marks the gap

1SELECT
2  e.emp_id,
3  e.dept_id,
4  d.dept_name
5FROM employees AS e
6LEFT JOIN departments AS d
7  ON e.dept_id = d.dept_id

employees3 rows

emp_id

dept_id

E-101

D-10

E-103

NULL

E-105

D-99

departments2 rows

dept_id

dept_name

D-10

Sales

NULL

Unassigned

Preparing the animation

The 3 employees go in and 3 rows come out. E-101 pairs with Sales. The NULL dept_id on E-103 never pairs with the NULL key of the Unassigned department, and E-105 points at D-99, which no department owns. Both survive with dept_name NULL, so the gap shows up where the match would have been.

Telling the 2 NULLs apart

The right side's NULL says 'no partner found'. Whether the left key was itself missing, or present and unmatched, is a question for the left table's own column, and a CASE expression can ask both questions at once.

	SELECT
	e.emp_id,
	e.dept_id,
	d.dept_name,
	CASE
	WHEN e.dept_id IS NULL THEN 'no department recorded'
	WHEN d.dept_id IS NULL THEN 'department not found'
	ELSE 'matched'
	END AS match_status
	FROM employees AS e
	LEFT JOIN departments AS d
	ON e.dept_id = d.dept_id

Result

emp_id	dept_id	dept_name	match_status
E-101	D-10	Sales	matched
E-103	NULL	NULL	no department recorded
E-105	D-99	NULL	department not found

3 rows

The CASE tests d.dept_id, the join key, in place of d.dept_name. A department row with a key but a NULL name would still be a match, and testing the name would label it unmatched. Test the key the ON clause compared, which every matched row carries.

The filter that undoes a LEFT JOIN

A LEFT JOIN protects its rows only until WHERE runs. WHERE keeps rows whose condition is TRUE, and every comparison against a NULL the join filled in is NULL. Ask for every employee outside Sales, then add the branch that keeps the gaps.

The comparison alone: every row whose dept_name is NULL evaluates to NULL and is discarded.

	SELECT
	e.emp_id,
	d.dept_name
	FROM employees AS e
	LEFT JOIN departments AS d
	ON e.dept_id = d.dept_id
	WHERE d.dept_name != 'Sales'

Result

emp_id	dept_name

0 rows

The first tab returns no rows. E-101 is in Sales and fails the test as it should. E-103 and E-105 have a NULL dept_name, so NULL != 'Sales' is NULL and WHERE discards them: the LEFT JOIN kept them and the next clause threw them out. The second tab's OR d.dept_name IS NULL brings both back.

Counting after a join

After a LEFT JOIN, COUNT(*) counts every left row and COUNT of a right-side key counts only the matched ones. The difference is the number of rows an INNER JOIN would have erased without a trace.

	SELECT
	COUNT(*) AS employees,
	COUNT(d.dept_id) AS with_department,
	COUNT(*) - COUNT(d.dept_id) AS without_department
	FROM employees AS e
	LEFT JOIN departments AS d
	ON e.dept_id = d.dept_id

Result

employees	with_department	without_department
3	1	2

1 row

With INNER JOIN in place of LEFT JOIN the same query returns 1, 1 and 0. The 2 unmatched employees are counted nowhere, and nothing in the result shows they exist.

Complete the SQL

> Each row of transactions carries a merchant_id that is NULL when the card terminal sent none. Keep every transaction and label it 'no merchant id', 'merchant not found' or 'matched'.

COALESCE for defaults

Daily Life

Interviews

First non-NULL wins

COALESCE takes a list of expressions and returns the first one that is not NULL, reading left to right. If every argument is NULL, it returns NULL. It is a CASE expression in compact form: COALESCE(a, b, c) means CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. The function skips NULL and only NULL; a 0 or an empty string is a value and is returned like any other.

Expression	Returns	Why
COALESCE(NULL, 7)	7	first argument is NULL, second is a value
COALESCE(3, 7)	3	first argument already has a value
COALESCE(NULL, NULL, 'x')	'x'	third argument is the first non-NULL
COALESCE(NULL, NULL)	NULL	nothing to return
COALESCE(0, 7)	0	0 is a value, only NULL is skipped
COALESCE('', 'x')	''	an empty string is a value too

1SELECT
2  id,
3  COALESCE(phone, email, 'Unknown') AS contact
4FROM contacts

contacts4 rows

phone

C-1

555-1234

NULL

C-2

NULL

bob@mail.com

C-3

NULL

C-4

555-9999

dan@mail.com

Preparing the animation

C-1 has a phone and the phone wins. C-2 has no phone, so the email comes through. C-3 has neither, and the literal 'Unknown' at the end guarantees that the contact column is never NULL. C-4 has both and still returns the phone: argument order is priority order, and COALESCE never looks past the first value it finds.

The arguments must share a type

COALESCE returns one column, so every argument has to fit one type. COALESCE(age, 'unknown') fails, because INTEGER and VARCHAR cannot share a column; cast the column first and the default fits. Numeric types widen instead of failing: COALESCE(quantity, 0.0) with an INTEGER quantity returns a DECIMAL, because the literal 0.0 is a decimal.

	SELECT
	user_id,
	COALESCE(CAST(age AS VARCHAR), 'unknown') AS age_label
	FROM users

COALESCE inside an aggregate changes the answer

SUM skips NULL, so SUM(COALESCE(tip, 0)) gives the same total as SUM(tip) whenever at least one tip is present. When every tip in a group is NULL, SUM returns NULL and the COALESCE version returns 0: missing versus zero again. AVG and COUNT go further, since COALESCE turns a skipped row into a counted one. The rides table has 5 rides, 2 with no recorded tip.

	SELECT
	SUM(tip) AS sum_tip,
	SUM(COALESCE(tip, 0)) AS sum_tip_zeroed,
	AVG(tip) AS avg_tip,
	AVG(COALESCE(tip, 0)) AS avg_tip_zeroed
	FROM rides

Result

sum_tip	sum_tip_zeroed	avg_tip	avg_tip_zeroed
7	7	2.3333333333333335	1.4

1 row

Both sums are 7.0, but the averages differ: 7 over the 3 tipped rides is 2.33, and 7 over all 5 rides is 1.40. The first is the average tip among riders who tipped; the second is the average tip per ride, counting an unrecorded tip as no tip. COALESCE records which one the report meant, and the reader of the number should know.

Multiple Choice

The reviews table has 10 rows, 3 of them with a NULL rating, and the other 7 ratings add up to 28. What does AVG(COALESCE(rating, 0)) return?

Labelling the NULL group

GROUP BY on a column that holds NULL creates a group whose key is NULL, and a report draws it as a blank cell that readers mistake for a formatting error. COALESCE in the SELECT list gives the group a name. Grouping by the raw column works, since an expression over a grouped column is allowed in SELECT. Grouping by the COALESCE expression, as below, differs in one case: if the label matches a real value, the 2 groups merge into one.

	SELECT
	COALESCE(carrier_code, 'no carrier') AS carrier,
	COUNT(*) AS shipments
	FROM shipments
	GROUP BY COALESCE(carrier_code, 'no carrier')
	ORDER BY shipments DESC, carrier

Result

carrier	shipments
FDX	3
N/A	3
NWX	3
no carrier	1

4 rows

Shipment S-10 now sits in a group called 'no carrier'. The 3 'N/A' shipments keep a group of their own, because COALESCE touches NULL and nothing else. Turning 'N/A' into something COALESCE can see is the next section's job.

Complete the SQL

> The support_tickets table stores priority as VARCHAR, NULL for a ticket nobody prioritised. Count tickets per priority with the NULL group labelled 'unset', busiest group first.

NULLIF for sentinel values

Daily Life

Interviews

Turning a chosen value into NULL

NULLIF(a, b) compares a with b. When they are equal it returns NULL; otherwise it returns a unchanged. As a CASE it reads CASE WHEN a = b THEN NULL ELSE a END. It is the tool for the sentinels of the first section: name the code, and every row holding it becomes a true missing value that aggregates skip, COUNT(column) leaves out and IS NULL finds.

Expression	Returns	Why
NULLIF(0, 0)	NULL	the 2 arguments are equal
NULLIF(34, 0)	34	different, so the first argument comes back
NULLIF('N/A', 'N/A')	NULL	equal strings
NULLIF('n/a', 'N/A')	'n/a'	string comparison is case sensitive
NULLIF(NULL, 0)	NULL	the first argument was already NULL
NULLIF(7, NULL)	7	7 = NULL is NULL, never TRUE, so 7 comes back

1SELECT
2  user_id,
3  age,
4  NULLIF(age, 0) AS clean_age
5FROM legacy_users

legacy_users5 rows

user_id

age

Preparing the animation

Users 2 and 4 held the sentinel 0 and now hold NULL in clean_age; users 1, 3 and 5 pass through. The raw age column stays beside it, so each row records both what was stored and what was known.

Multiple Choice

The accounts.status column holds, row by row: 'active', 'closed', NULL, 'Active'. What does this query return, row by row?

	SELECT
	NULLIF(status, 'active') AS status_flag
	FROM accounts

Repairing an average

Back to legacy_users, where the first section showed AVG(age) reporting 21.2 because of 2 fake zeros. NULLIF inside the aggregate repairs the number without filtering a single row out of the query.

	SELECT
	AVG(age) AS avg_with_zeros,
	AVG(NULLIF(age, 0)) AS avg_known_ages,
	COUNT(age) AS rows_with_age,
	COUNT(NULLIF(age, 0)) AS rows_with_known_age
	FROM legacy_users

Result

avg_with_zeros	avg_known_ages	rows_with_age	rows_with_known_age
21.2	35.333333333333336	5	3

1 row

Same 5 rows, and NULLIF restores the figures the WHERE filter found, 35.3 and 3, while every row stays in play. WHERE age > 0 would drop the 2 users entirely, with their cities, orders and signup dates; NULLIF blanks one field and keeps the rest of the row.

The one-value limit

NULLIF names exactly 1 value. A column with 2 sentinels needs 2 nested calls, and a column with 3 or more reads better as a CASE with IN, which the filtering lessons already gave you. A range of impossible values, such as an age below 0 or above 120, also belongs in a CASE, since NULLIF compares for equality and nothing else.

	SELECT
	user_id,
	NULLIF(NULLIF(country, 'N/A'), 'unknown') AS country_nested,
	CASE
	WHEN country IN ('N/A', 'unknown', '') THEN NULL
	ELSE country
	END AS country_case
	FROM users

The nested form evaluates inside out: the inner NULLIF clears 'N/A', and the outer one receives either a country or a NULL and clears 'unknown'. A NULL reaching the outer call stays NULL, since NULL = 'unknown' is never TRUE.

Division by zero

Dividing by 0 raises an error and stops the whole query. A column of counts holds 0 for every group with nothing to count, so a rate computed from it fails on the first empty group. NULLIF on the divisor turns the 0 into NULL, and anything divided by NULL is NULL, which is the right rate for a campaign nobody saw. The CAST to DOUBLE stops integer division from rounding 50 / 1000 down to 0.

	SELECT
	campaign_id,
	clicks,
	impressions,
	CAST(clicks AS DOUBLE) / NULLIF(impressions, 0) AS click_rate
	FROM campaigns

Result

campaign_id	clicks	impressions	click_rate
CMP-A	50	1000	0.05
CMP-B	0	0	NULL
CMP-C	30	500	0.06

3 rows

CMP-A and CMP-C return 0.05 and 0.06. CMP-B returns NULL in place of an error, and the other 2 rows arrive intact. An AVG over click_rate skips CMP-B and reports 0.055, the average rate among campaigns that were shown.

TIP

Keep the raw column next to its NULLIF twin while you learn a dataset: 0 and NULL side by side show how many rows the sentinel touched, which is the first thing anyone asks about the clean numbers.

COALESCE and NULLIF patterns

Daily Life

Interviews

Clean first, then default

The 2 functions nest. COALESCE(NULLIF(description, ''), 'No description') runs the inner call first: an empty string becomes NULL and a real description passes through. The outer call then fills every NULL, whether it arrived as NULL or came from NULLIF, with one default. The reverse nesting, NULLIF(COALESCE(description, ''), ''), turns NULL into '' and then '' into NULL, so both kinds of missing collapse into one NULL and nothing is filled.

	SELECT
	product_id,
	description,
	COALESCE(NULLIF(description, ''), 'No description') AS display_text,
	NULLIF(COALESCE(description, ''), '') AS normalized
	FROM products

Result

product_id	description	display_text	normalized
P-1	Steel bottle 750ml	Steel bottle 750ml	Steel bottle 750ml
P-2		No description	NULL
P-3	NULL	No description	NULL
P-4	N/A	N/A	N/A

4 rows

P-2 and P-3 arrive as '' and NULL and leave as 'No description' in display_text and as NULL in normalized. P-4 slips through both columns as 'N/A', because the query named '' as the only sentinel. A second NULLIF or a CASE with IN would catch it, once a profile has found it.

Multiple Choice

The country column holds, row by row: 'US', N/A, NULL, ''. What does COALESCE(NULLIF(country, N/A), 'Unknown') return, row by row?

A safe divisor with a default

The rate from the last section returned NULL for the campaign with no impressions. A dashboard sorting campaigns by rate wants a number in every row, so COALESCE wraps the division and supplies 0, which changes any AVG computed across campaigns.

	SELECT
	campaign_id,
	CAST(clicks AS DOUBLE) / NULLIF(impressions, 0) AS click_rate,
	COALESCE(CAST(clicks AS DOUBLE) / NULLIF(impressions, 0), 0) AS click_rate_or_zero
	FROM campaigns

Result

campaign_id	click_rate	click_rate_or_zero
CMP-A	0.05	0.05
CMP-B	NULL	0
CMP-C	0.06	0.06

3 rows

CMP-B reads NULL in one column and 0.0 in the other. An AVG over click_rate gives 0.055, the mean of the 2 campaigns that ran. An AVG over click_rate_or_zero gives 0.0367, because a campaign nobody saw now counts as a campaign nobody clicked. The 0 belongs in the column a sort or a display needs; the NULL belongs in the column an average will read.

Label the unmatched side of a LEFT JOIN

A LEFT JOIN marks every unmatched row with NULL on the right side, and COALESCE on a right-side column turns that mark into a label. The label covers both gaps the second section separated, a missing key and a key with no matching row; keep that section's CASE when a report needs to tell them apart.

1SELECT
2  e.emp_id,
3  COALESCE(d.dept_name, 'Unassigned') AS dept_name
4FROM employees AS e
5LEFT JOIN departments AS d
6  ON e.dept_id = d.dept_id

employees3 rows

emp_id

dept_id

E-101

D-10

E-103

NULL

E-105

D-99

departments2 rows

dept_id

dept_name

D-10

Sales

D-20

Finance

Preparing the animation

E-101 keeps Sales. E-103, with no key, and E-105, pointing at a department that does not exist, both come out as Unassigned: the COALESCE runs after the join, sees NULL in dept_name for both and supplies the same text.

One query over the shipments table

The shipments table from the first section carries 2 sentinels, -1 for an unweighed parcel and 'N/A' for an unknown carrier, plus 1 true NULL carrier. The query below converts both sentinels to NULL once in a CTE, then LEFT JOINs to carriers, labels the unmatched side with COALESCE and aggregates the cleaned column.

	WITH clean AS (
	SELECT
	shipment_id,
	NULLIF(carrier_code, 'N/A') AS carrier_code,
	NULLIF(weight_kg, - 1) AS weight_kg
	FROM shipments
	)

	SELECT
	COALESCE(c.carrier_name, 'Unassigned') AS carrier,
	COUNT(*) AS shipments,
	COUNT(s.weight_kg) AS weighed,
	AVG(s.weight_kg) AS avg_weight_kg
	FROM clean AS s
	LEFT JOIN carriers AS c
	ON s.carrier_code = c.carrier_code
	GROUP BY COALESCE(c.carrier_name, 'Unassigned')
	ORDER BY shipments DESC, carrier

Result

carrier	shipments	weighed	avg_weight_kg
Unassigned	4	1	9
Fastdrop	3	2	7.25
Northwind Express	3	3	4.666666666666667

3 rows

Unassigned holds 4 shipments: the 3 that said 'N/A' and S-10 with its true NULL, one group because the CTE made them the same value before the join. Only 1 of the 4 was weighed, at 9.0 kilograms. Fastdrop weighed 2 of its 3 parcels and averages 7.25; Northwind Express weighed all 3 for 4.67. With the raw -1 in the AVG, Fastdrop would read 4.5 and Unassigned 1.5. The weighed column beside shipments puts the missing data in the report itself.

✓Do

Convert sentinels to NULL in a CTE, then join and aggregate the clean columns
Name the default for what it means: 'Unassigned', 'No description', 'no carrier'
Report COUNT(*) beside COUNT(clean_column) so the reader sees how much was missing
Put NULLIF on any divisor that can hold 0, and decide separately whether the rate defaults

✗Don't

COALESCE a measure to 0 before AVG unless the report means 'missing counts as zero'
Filter a LEFT JOIN's right-side column in WHERE without an IS NULL branch
Swap the nesting by habit: NULLIF(COALESCE(x, ''), '') blanks and fills nothing
Replace an old sentinel with a new one such as -1; the next reader will have to find it

Complete the SQL

> In tickets, resolution_hours holds -1 for a ticket still open and team holds '' for an unassigned one. Clean both sentinels once in a CTE, then report each team's ticket count, resolved count and average resolution hours, with the empty team labelled 'Unassigned'.

❯❯❯PUTTING IT ALL TOGETHER

> A food delivery company publishes a weekly restaurant report from a restaurant_weeks table, one row per restaurant per week with order_count, refund_count and prep_minutes, joined to the restaurants table for names and sorted by refund rate. A restaurant paused for the week still gets a row with order_count 0, prep_minutes is -1 when the kitchen confirmed no order that week, and rows for restaurants that left the platform have no match in restaurants.

The -1 in prep_minutes is a sentinel: a GROUP BY prep_minutes with COUNT(*) shows the spike, and NULLIF(prep_minutes, -1) in a CTE makes it a NULL that AVG skips while the row stays in every other total.

Rows for restaurants that left the platform need a LEFT JOIN to restaurants so their orders still count toward volume; COUNT(*) minus COUNT(r.restaurant_id) reports how many rows have no restaurant on file.

COALESCE(r.name, 'Closed restaurant') labels the unmatched rows, and any WHERE on a restaurant column carries an OR r.name IS NULL branch so the label survives to the output.

The refund rate divides refund_count by order_count, so NULLIF(order_count, 0) keeps the query running on a paused restaurant's week; COALESCE to 0 belongs only in the sort column, where every row needs a number, and stays out of the AVG across restaurants.

KEY TAKEAWAYS

A sentinel is a real value standing in for a missing one, and every aggregate treats it as real; profile a column with GROUP BY and COUNT(*) before you average it.

A NULL key pairs with nothing in a join, including another NULL; INNER JOIN drops the row and LEFT JOIN keeps it with NULLs on the right side.

A WHERE comparison on a LEFT JOIN's right-side column discards the NULL rows the join preserved; an IS NULL branch brings them back.

COALESCE returns the first non-NULL argument; inside AVG and COUNT it changes the denominator, so defaulting a measure to 0 is a decision about what the number means.

NULLIF(a, b) returns NULL when a equals b, turning one named sentinel into a true NULL that aggregates skip and that a divisor can safely hold.

COALESCE(NULLIF(x, sentinel), default) cleans and fills in one expression; the reverse nesting collapses both kinds of missing into one NULL and fills nothing.

Spot disguised missing data, keep rows alive through joins, and move values into and out of NULL on purpose.

COALESCE: NULL won't fix itself

Category: SQL
Difficulty: intermediate
Duration: 31 minutes
Challenges: 13 hands-on challenges

Topics covered: Sentinel values and fake NULLs, NULLs in INNER and LEFT JOINs, COALESCE for defaults, NULLIF for sentinel values, COALESCE and NULLIF patterns

Lesson Sections

Sentinel values and fake NULLs (concepts: Null Handling)
What a sentinel is A sentinel is an ordinary value a system wrote in place of a missing one. The column was declared NOT NULL, a form refused to submit without the field, or a CSV export had no way to write NULL. The table now holds a number or a string that looks like data and means 'we never knew'. Every aggregate from the last lesson treats a sentinel as real. NULL is skipped by COUNT(age), AVG(age) and MIN(age); a 0 is included by all 3. A column of fake zeros gives a lower average, a wrong
NULLs in INNER and LEFT JOINs (concepts: Nulls in Joins)
How a join pairs rows An INNER JOIN returns the pairs and nothing more. A LEFT JOIN returns the pairs, then adds back every row of the left table that found no partner, with the right table's columns filled with NULL for those rows. INNER JOIN drops the row The 4 orders meet 4 customers and 2 rows come out. ORD-1 and ORD-2 find Alice and Bob. The NULL customer_id on ORD-3 makes every comparison NULL, so it pairs with nobody, including the Guest customer whose key is also NULL. ORD-4 carries C-10
COALESCE for defaults (concepts: Null Defaulting)
First non-NULL wins C-1 has a phone and the phone wins. C-2 has no phone, so the email comes through. C-3 has neither, and the literal 'Unknown' at the end guarantees that the contact column is never NULL. C-4 has both and still returns the phone: argument order is priority order, and COALESCE never looks past the first value it finds. The arguments must share a type COALESCE inside an aggregate changes the answer Both sums are 7.0, but the averages differ: 7 over the 3 tipped rides is 2.33, and
NULLIF for sentinel values (concepts: Null Substitution)
Turning a chosen value into NULL Users 2 and 4 held the sentinel 0 and now hold NULL in clean_age; users 1, 3 and 5 pass through. The raw age column stays beside it, so each row records both what was stored and what was known. Repairing an average Back to legacy_users, where the first section showed AVG(age) reporting 21.2 because of 2 fake zeros. NULLIF inside the aggregate repairs the number without filtering a single row out of the query. The one-value limit NULLIF names exactly 1 value. A co
COALESCE and NULLIF patterns (concepts: Null Defaulting)
Clean first, then default P-2 and P-3 arrive as '' and NULL and leave as 'No description' in display_text and as NULL in normalized. P-4 slips through both columns as 'N/A', because the query named '' as the only sentinel. A second NULLIF or a CASE with IN would catch it, once a profile has found it. A safe divisor with a default The rate from the last section returned NULL for the campaign with no impressions. A dashboard sorting campaigns by rate wants a number in every row, so COALESCE wraps