MODULE 2 · SESSION 12

NULL Handling:
COALESCE, NULLIF & 3VL

Defensive fallback cascades, zero-division shields, horizontal row bounds with GREATEST & LEAST, three-valued logic truth matrices, and NULL-safe equality in PostgreSQL 18 on RetailMart.

23

Scenes

1

Practice Arena

4

Practice Drills

5

Interview Questions

Today’s Agenda — NULL Handling & 3VL

Four pillars: three-valued logic, COALESCE fallbacks, zero-division guards, and NULL-safe equality

Pillar 01

❓ The Nature of NULL & 3VL

01 What is NULL: unknown or missing state

02 Three-valued logic: TRUE, FALSE, UNKNOWN

03 Testing predicates: IS NULL & IS NOT NULL

Pillar 02

🛡 COALESCE Fallbacks

04 Fallback chains: COALESCE(val, fallback)

05 Short-circuit: first non-NULL returned

06 Real RetailMart data: 1,50,316 guest visits

Pillar 03

🚫 NULLIF & Division Safety

07 Sentinel conversion: NULLIF(val, sentinel)

08 Zero-division guard: x / NULLIF(y, 0)

09 Blank cleaning: NULLIF(TRIM(str), '')

Pillar 04

🔍 Safe Comparisons & Aggregates

10 NULL-safe equality: IS DISTINCT FROM

11 The NOT IN trap: NULL poisoning entire sets

12 Aggregate mechanics: COUNT(col) vs COUNT(*)

💡

Why this matters: RetailMart contains genuine missing values: 1,50,316 page views lack customer IDs (guest shoppers), and 14,982 shipments lack delivered dates. Using standard equality on NULL produces UNKNOWN, silently dropping rows from reports and freezing production queries.

Where We Left Off: Conditional Branching to NULL Mechanics

Bridging declarative conditional logic and the subtleties of three-valued truth

🧭 SESSION 11 • COMPLETED

Branching Logic & Casting

-- Session 11: CASE expressions & explicit type casting

SELECT order_id, net_total,

CASE WHEN net_total >= 10000 THEN 'VIP'

ELSE 'Regular' END AS tier

FROM sales.orders

ORDER BY order_id LIMIT 3;

-- Evaluated row by row into single values

Session 11 introduced CASE WHEN branching and explicit casting. But what happens when an expression encounters missing or unknown values? Standard comparisons fail under Three-Valued Logic.

✓ Branching mastered; missing data mechanics await

❓ SESSION 12 • THE MISSION

NULL Handling & Safe Equality

-- “Replace missing values & guard division”

SELECT view_id,

COALESCE(customer_id::TEXT, 'Guest') AS visitor,

100.0 / NULLIF(0, 0) AS safe_ratio

FROM web_events.page_views LIMIT 3;

-- Guest, 4602, 43290; 100 / 0 would crash, NULLIF gives NULL

PostgreSQL provides dedicated NULL operators (IS NULL, COALESCE, NULLIF, and IS DISTINCT FROM). We master three-valued logic, protect division from crashing, and prevent the disastrous NOT IN trap.

❓ Today: COALESCE, NULLIF, IS DISTINCT FROM, 3VL

💡

The Core Rule: NULL represents the absence of a value. It never equals, nor does it not equal, anything else. Always use IS NULL, COALESCE, or IS DISTINCT FROM.

❓

The Blacklist Blackout Disaster NOT IN POISONING • THREE-VALUED LOGIC COLLAPSE

SUPPLY CHAIN • DISPATCH LOCKOUT EXAMPLE STORY • NAMES & NUMBERS MADE UP ⚠ ₹45 LAKH FROZEN AT DOCKS

STEP 01 • THE INCIDENT

warehouse-ops 06:45 AM

“Dispatch engine generated zero shipment manifests today! 450 loaded trucks are idling at the dock gates. ₹45 lakh in time-sensitive supplier dispatches are blocked!”

Filter checked WHERE vendor_id NOT IN (SELECT vendor_id FROM audit_blacklist).

STEP 02 • ROOT CAUSES

⚠ NOT IN with Hidden NULL

NOT IN (101, 102, NULL)

The audit blacklist subquery contained one unassigned vendor record where vendor_id was NULL. The entire list was poisoned.

⚡ Logical Collapse to UNKNOWN

x != NULL Evaluates UNKNOWN

NOT IN expands into AND chains: vendor != 101 AND vendor != NULL. Because comparison with NULL is UNKNOWN, the entire condition evaluated to UNKNOWN.

💸 ₹45 Lakh in Shipments Frozen

Zero Dispatches Released

WHERE only passes rows with TRUE. Zero rows passed the filter, locking all warehouse dispatches and freezing ₹45 lakh in supplier shipments.

STEP 03 • THE FIX

FILTER NULLS FIRST

🐙 Safe NOT IN Clause

-- Blacklist: suppliers 1, 2 + one blank row
-- NOT IN (1, 2, NULL) returns 0 rows
SELECT
  shipment_id,
  supplier_id,
  status
FROM supply_chain.shipments
WHERE supplier_id NOT IN (1, 2)
ORDER BY shipment_id
LIMIT 10;
-- NULL kept out: 1,949 dispatches released

✓ Excludes NULL Blacklist Rows Dispatches Released

STEP 04 • CORE PRINCIPLE

The Moral: A single NULL in a NOT IN list evaluates every comparison to UNKNOWN, returning zero rows. Always filter out NULLs with WHERE col IS NOT NULL or use NOT EXISTS.

Mapping Missing Data to PostgreSQL Primitives

Every crisis in the story translates directly into an essential defensive SQL function.

🔣 Fallback Cascading

Problem: Missing customer phone or address attributes render blank, broken UI profiles.
PostgreSQL Tool: COALESCE(phone, alt_phone, 'Not Provided').
Evaluates arguments left-to-right and halts on the first non-NULL value.

💣 Zero-Division Defense

Problem: Dividing by zero (price = 0 or units = 0) causes fatal query abort.
PostgreSQL Tool: COALESCE(val / NULLIF(denom, 0), 0.0).
Converts dangerous zeroes into benign NULLs that evaluate cleanly without crashing.

🛡 Safe Equality & 3VL

Problem: Standard comparison (col != 'Gold') drops NULLs because NULL != 'Gold' is UNKNOWN.
PostgreSQL Tool: col IS DISTINCT FROM 'Gold'.
Treats NULL as an ordinary comparable value for change detection and auditing.

Visual Studio: The COALESCE Fallback Cascade

Click contact scenarios to watch PostgreSQL evaluate arguments from left to right.

SIMULATE CONTACT DATA:

Work phone exists: COALESCE stops on Arg 1 and ignores remaining fallbacks.

Arg 1: work_phone

→

Arg 2: mobile_phone

→

Arg 3: home_phone

→

Arg 4: 'Not Provided'

Resolved Output: +91 98765 43210 (Work Phone)

COALESCE: The Null Fallback Primitive

Evaluating arbitrary argument cascades to guarantee reliable, report-ready non-null values.

Pillar 01

Left-to-Right Cascade

COALESCE(val1, val2, ..., default)
Evaluates arguments in sequence and returns the first non-NULL value. If all are NULL, returns NULL (or your provided default literal).

Pillar 02

Short-Circuit Execution

Evaluation halts immediately on the first non-NULL argument. Subsequent expressions (including expensive subqueries or functions) are never executed!

Pillar 03

String Concat Shield

Standard SQL string concatenation ('₹ ' || price) propagates NULL: if price is NULL, the result is NULL. COALESCE ensures a clean default string before concatenation.

QUERY1,50,316 Guest Views Get a Clean Label

SELECT
  view_id,
  page_url,
  customer_id,
  COALESCE(
    customer_id::TEXT,
    'Guest'
  ) AS visitor
FROM web_events.page_views
ORDER BY view_id
LIMIT 10;

Visual Studio: Zero-Division Shielding

Compare naive division crashes against NULLIF defensive engineering.

TEST DENOMINATOR:

Naive: 100 / 0 → FATAL: division by zero! Entire query aborted.

🛡 PRODUCTION SAFEGUARD Any division by NULL evaluates to NULL without throwing an exception. NULLIF converts dangerous 0s into safe NULLs!

Zero-Division Shield: NULLIF in Production Math

Why dividing by NULL is safe, elegant, and standard in analytical engineering.

Mechanism 01

Division by Zero Fatal Crash

In PostgreSQL, 100 / 0 raises a fatal runtime exception: division by zero. A single free promotional item (price = 0) crashes an entire enterprise report.

Mechanism 02

Division by NULL Is Clean

In relational math, 100 / NULL evaluates to NULL without raising an exception! NULLIF(denom, 0) converts dangerous 0s to benign NULLs.

Mechanism 03

The Atomic Shield Pair

Combine NULLIF with COALESCE: COALESCE(val / NULLIF(denom, 0), 0.0). Computes the metric, shields from crashes, and supplies a business default.

QUERYSafe Percentage Ratios with COALESCE + NULLIF

SELECT product_id,
  product_name,
  price,
  cost_price,
  ROUND(((price - cost_price) / NULLIF(price, 0)) * 100.0, 1) AS margin_pct,
  COALESCE(ROUND(((price - cost_price) / NULLIF(price, 0)) * 100.0, 1), 0.0) AS safe_margin_pct
FROM products.products
ORDER BY price DESC
LIMIT 10;

Visual Studio: Three-Valued Logic Matrix

Click boolean operators to inspect how UNKNOWN behaves under SQL logic.

INSPECT TRUTH TABLE:

AND Operations:
TRUE AND UNKNOWN → UNKNOWN
FALSE AND UNKNOWN → FALSE
UNKNOWN AND UNKNOWN → UNKNOWN

⚠ THE WHERE FILTER LAW WHERE clauses accept rows ONLY if the condition evaluates strictly to TRUE. UNKNOWN rows are silently discarded!

Truth in SQL: Three-Valued Logic & IS DISTINCT FROM

Why NULL = NULL is never TRUE, and how IS DISTINCT FROM fixes change detection.

Concept 01

3VL: Three-Valued Logic

SQL booleans have three states: TRUE, FALSE, and UNKNOWN. Any standard comparison (=, !=, >, <) involving NULL evaluates to UNKNOWN!

Concept 02

WHERE Drops UNKNOWN

A WHERE clause only accepts rows where the expression evaluates strictly to TRUE. If a condition evaluates to UNKNOWN, the row is discarded silently!

Concept 03

IS DISTINCT FROM

Treats NULL as an ordinary value for comparison! NULL IS NOT DISTINCT FROM NULL is TRUE, and 'Gold' IS DISTINCT FROM NULL is TRUE.

QUERYCompare Standard Equality vs NULL-Safe Distinct Equality

SELECT
  (NULL = NULL)
    AS null_eq_null,
  (NULL != NULL)
    AS null_neq_null,
  (NULL IS NOT DISTINCT FROM NULL)
    AS distinct_null_equality,
  ('Active' IS DISTINCT FROM NULL)
    AS val_distinct_from_null
LIMIT 1;

Pro Tips & Three-Valued Logic Pitfalls

Avoid the catastrophic NOT IN (NULL) trap and inequality filter omissions.

⚠ Mistake 1: The NOT IN (NULL) Catastrophe

Wrong: WHERE id NOT IN (SELECT bonus_id FROM ...) when bonus_id contains even ONE NULL!
Why: Expands to id != 1 AND id != NULL. Since id != NULL is UNKNOWN, the whole expression is UNKNOWN, returning 0 rows!
Fixed: Use NOT EXISTS or filter: WHERE bonus_id IS NOT NULL.

⚠ Mistake 2: Missing Rows with !=

Wrong: WHERE tier != 'Gold' assuming it returns Silver, Bronze, AND NULL.
Why: For rows where tier is NULL, NULL != 'Gold' is UNKNOWN, so PostgreSQL drops them!
Fixed: Use WHERE tier IS DISTINCT FROM 'Gold'.

✔ Pro Tip 1: IS NULL vs IS NOT DISTINCT FROM NULL

col IS NULL and col IS NOT DISTINCT FROM NULL produce the exact same boolean result. Use IS NULL for clarity when testing a single literal, and IS NOT DISTINCT FROM when comparing two dynamic columns.

✔ Pro Tip 2: NULLS FIRST vs NULLS LAST in ORDER BY

By default in PostgreSQL, ASC sorts place NULLs LAST, while DESC sorts place NULLs FIRST. Take explicit control with: ORDER BY price DESC NULLS LAST.

65%

🧠Practice Time

Visual Studio: GREATEST & LEAST vs Aggregates

Click evaluation scope to inspect horizontal row bounds versus vertical column aggregations.

EVALUATION SCOPE:

GREATEST(price, cost_price): Evaluates across columns for each single row independently. If one value is NULL, it selects the non-NULL value!

💡 ROW VS COLUMN SCOPE GREATEST and LEAST compare columns across a single row; MAX and MIN aggregate rows down a single column. Never confuse horizontal functions with vertical aggregates!

Horizontal vs Vertical: Null Behavior Across Dimensions

Distinguishing row-level multi-column functions from column-level multi-row aggregations.

Dimension 01

GREATEST & LEAST (Horizontal)

Evaluates row by row across multiple column arguments. In PostgreSQL, GREATEST(10, NULL) returns 10! NULL is skipped unless all arguments are NULL.

Dimension 02

MAX & MIN (Vertical)

Evaluates column by column down across thousands of rows. Aggregates skip NULLs: COUNT(col), SUM, AVG, MAX, MIN. Only COUNT(*) counts every row.

Dimension 03

String Cleansing with NULLIF

Blank spaces (' ') are NOT NULL! Use NULLIF(TRIM(col), '') to convert empty text and blank spaces into clean SQL NULLs before processing.

QUERYRow Bounds with GREATEST and Safe Variance Check

SELECT product_id, product_name, price, cost_price,
  GREATEST(price, cost_price) AS max_valuation,
  LEAST(price, cost_price) AS min_valuation,
  (price IS DISTINCT FROM cost_price) AS has_price_variance
FROM products.products
WHERE cost_price IS NOT NULL
ORDER BY product_id
LIMIT 10;

Interview Studio: Master NULL & 3VL Questions

Five core missing data questions interviewers ask, with production-grade answers.

Q1 · Standards & Portability

What is the technical and architectural difference between COALESCE, IFNULL, and NVL?

Answer to give:
COALESCE is the ANSI SQL standard function supported by all major database engines (PostgreSQL, MySQL, Oracle, SQL Server, BigQuery). It accepts an arbitrary number of arguments (cascade chaining) and evaluates lazily with short-circuiting. NVL(a, b) is Oracle-specific and takes exactly 2 arguments. IFNULL(a, b) is MySQL/SQLite-specific and takes exactly 2 arguments. In modern engineering, always prefer ANSI COALESCE for portable, expressive code.

Self-Check Studio: 6 Defensive NULL Retention Cards

Click any flashcard to flip and test your understanding of PostgreSQL 3VL rules.

CLICK TO FLIP • SELF-CHECK

What are the exact return values of NULLIF(50, 50) and NULLIF(50, 0)?

ANSWER: NULLIF(50, 50) returns NULL (operands equal). NULLIF(50, 0) returns 50 (operands not equal). This makes NULLIF the ideal shield against zero-division.

CLICK TO FLIP • SELF-CHECK

What does COALESCE(NULL, NULL, 42, 99) evaluate to?

ANSWER: 42. COALESCE evaluates expressions from left to right and halts on the first non-NULL value, ignoring any subsequent arguments.

CLICK TO FLIP • SELF-CHECK

Why does WHERE tier != 'Gold' fail to return customers whose tier is NULL?

ANSWER: In 3VL, NULL != 'Gold' evaluates to UNKNOWN. SQL WHERE clauses only keep rows that evaluate strictly to TRUE. Use WHERE tier IS DISTINCT FROM 'Gold'.

CLICK TO FLIP • SELF-CHECK

What is the result of NOT (UNKNOWN) in three-valued logic?

ANSWER: UNKNOWN. Negating an unknown state remains unknown. This is why WHERE NOT (status = 'Active') still drops rows where status is NULL!

CLICK TO FLIP • SELF-CHECK

How does GREATEST(10, NULL, 25) behave in PostgreSQL?

ANSWER: Returns 25. Unlike mathematical addition (+) which propagates NULL, GREATEST skips NULLs as long as at least one argument is non-null.

CLICK TO FLIP • SELF-CHECK

Why does WHERE id NOT IN (10, 20, NULL) return 0 rows for the entire table?

ANSWER: Expands to id != 10 AND id != 20 AND id != NULL. Since id != NULL is UNKNOWN, the entire AND conjunction evaluates to UNKNOWN, dropping all rows.

Key Takeaways: The 6 Defensive NULL Laws

Universal principles for writing resilient SQL queries and bulletproof missing data pipelines.

01

Shield Denominators with NULLIF

Never divide by raw columns. Wrap the divisor in NULLIF(val, 0) so zero-division cleanly produces NULL instead of aborting the query.

02

The Atomic Shield Pattern

Combine NULLIF with COALESCE: COALESCE(val / NULLIF(denom, 0), 0.0). Shields against crashes and guarantees clean, default-ready numeric metrics.

03

COALESCE for Hierarchical Fallbacks

Scans arguments left-to-right and halts at the first non-NULL value. Perfect for contact directory fallbacks, defaults, and multi-timestamp timelines.

04

The Lethal NOT IN (NULL) Trap

A single NULL inside a NOT IN subquery causes the entire filter to evaluate to UNKNOWN, returning zero rows! Keep NULLs out of the list, or use NOT EXISTS (Session 26).

05

WHERE Clauses Drop UNKNOWN

A WHERE filter only accepts rows evaluating strictly to TRUE. Both FALSE and UNKNOWN rows are dropped. Standard != drops NULL rows.

06

Use IS DISTINCT FROM for Safe Audits

Standard = and != fail in 3VL when operands are NULL. Use IS DISTINCT FROM for safe change-data-capture and audit discrepancy checks.

Quick Reference: Defensive NULL & 3VL Cheatsheet

Complete PostgreSQL defensive function index with interactive ▶ Run links.

COALESCE & NULLIF MECHANICS

COALESCE Fallback (First non-null)▶ Run

SELECT COALESCE(NULL, NULL, 'Fallback Value');

COALESCE Default (First arg exists)▶ Run

SELECT COALESCE('Work Phone', 'Not Provided');

NULLIF Equality (Returns NULL)▶ Run

SELECT NULLIF(100, 100);

NULLIF Non-Equal (Returns first)▶ Run

SELECT NULLIF(100, 0);

Safe Division Shield (No crash)▶ Run

SELECT 1000.0 / NULLIF(0, 0);

Atomic Shield Pair (Zero default)▶ Run

SELECT COALESCE(1000.0 / NULLIF(0, 0), 0.0);

3VL & COMPARISON CHEATSHEET

Standard Equality (Returns UNKNOWN)▶ Run

SELECT (NULL = NULL) AS eq_test;

Safe Equality (Returns TRUE)▶ Run

SELECT (NULL IS NOT DISTINCT FROM NULL) AS safe_eq LIMIT 1;

Safe Inequality (Returns TRUE)▶ Run

SELECT ('Gold' IS DISTINCT FROM NULL) AS safe_neq LIMIT 1;

Row Bounds (GREATEST / LEAST)▶ Run

SELECT GREATEST(120, 450, 85), LEAST(120, 450, 85);

GREATEST with NULL (Skips NULL)▶ Run

SELECT GREATEST(100, NULL, 50);

Clean Blank Spaces (Spaces to NULL)▶ Run

SELECT NULLIF(TRIM('   '), '') AS clean_str;

Take-Home Challenge: 7 Defensive Queries on RetailMart

Apply today's COALESCE, NULLIF, and IS DISTINCT FROM techniques in the playground, then check your counts against the ones given.

01

Customer Contact Directory

From web_events.page_views, show view_id, page_url and the visitor with COALESCE(customer_id::TEXT, 'Guest'): 1,50,316 of the 5,00,000 views are guests.

02

Safe Margin Ratio

From products.products, calculate profit margin percentage shielding against zero price: ROUND(((price - cost_price) / NULLIF(price, 0)) * 100, 2).

03

Shipment Milestone Audit

From sales.shipments, resolve the latest event date with COALESCE(delivered_date, shipped_date): 14,982 parcels fall back to shipped_date.

04

Distinct Price Variance

From products.products, detect products where price differs from cost_price with price IS DISTINCT FROM cost_price.

05

Horizontal Valuation Bounds

From products.products, compute GREATEST(price, cost_price) and LEAST(price, cost_price) for catalog valuation bounds.

06

Order Discount Shield

From sales.orders, calculate discount percentage: COALESCE(ROUND((discount_amount / NULLIF(gross_total, 0)) * 100, 2), 0.00).

07

Clean Blank Strings

From customers.customers, convert whitespace-only strings to genuine NULLs: NULLIF(TRIM(phone), '') (RetailMart has 0 blank phones; real form data has many).

✓

Ready to Execute?

Run each challenge in the interactive playground. Verify your results and review execution output.

Your SQL Journey: Session 12 Completed

Track your progress across the 59-session SQL course.

MODULE 1 S01–S08: Foundation & Sorting COMPLETED ✓

MODULE 2 · S09–S11 Strings, Dates & CASE COMPLETED ✓

MODULE 2 · S12 NULL Handling & 3VL YOU ARE HERE ●

MODULE 2 · S13 Aggregate Functions UP NEXT →

Next Session Preview: Aggregate Functions

Coming next in Module 2: Compressing Lakhs of Rows into Executive Business Metrics.

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX & STRING_AGG

MODULE 2 · SESSION 13

Learn how to compress 24 lakh rows into executive KPI summaries using COUNT, SUM, AVG, MIN, MAX, and STRING_AGG. Discover how aggregate functions handle NULLs and preview group aggregation.

View Live Schedule → Review From Beginning ↺

NULL Handling:COALESCE, NULLIF & 3VL

Today’s Agenda — NULL Handling & 3VL

Where We Left Off: Conditional Branching to NULL Mechanics

Branching Logic & Casting

NULL Handling & Safe Equality

Mapping Missing Data to PostgreSQL Primitives

Visual Studio: The COALESCE Fallback Cascade

COALESCE: The Null Fallback Primitive

Visual Studio: Zero-Division Shielding

Zero-Division Shield: NULLIF in Production Math

Visual Studio: Three-Valued Logic Matrix

Truth in SQL: Three-Valued Logic & IS DISTINCT FROM

Pro Tips & Three-Valued Logic Pitfalls

Visual Studio: GREATEST & LEAST vs Aggregates

Horizontal vs Vertical: Null Behavior Across Dimensions

Interview Studio: Master NULL & 3VL Questions

Self-Check Studio: 6 Defensive NULL Retention Cards

Key Takeaways: The 6 Defensive NULL Laws

Quick Reference: Defensive NULL & 3VL Cheatsheet

Take-Home Challenge: 7 Defensive Queries on RetailMart

Your SQL Journey: Session 12 Completed

Next Session Preview: Aggregate Functions

NULL Handling:
COALESCE, NULLIF & 3VL