Lesson 38 of 61

5 mins read

SQL Null Functions: COALESCE, IFNULL, ISNULL & NULLIF

In Plain English

SQL provides dedicated scalar functions to manage missing data gracefully: `COALESCE(val1, val2, ...)` returns the first non-null value from an argument list; `NULLIF(a, b)` returns `NULL` if arguments are equal (crucial for preventing division-by-zero errors).

The Mental Model

"`COALESCE` is a prioritized backup contact list: "Try calling mobile phone; if missing (NULL), try home phone; if missing, fall back to company main desk.""

Deep Dive: How It Works

COALESCE (ANSI Universal): `COALESCE(work_email, personal_email, '[email protected]')`. Accepts unlimited arguments, evaluating left-to-right.

Engine Variants: `IFNULL(a, b)` (MySQL/SQLite 2-arg shorthand), `ISNULL(a, b)` (SQL Server), `NVL(a, b)` (Oracle). `COALESCE` is universally portable across ALL engines.

Preventing Division-by-Zero with NULLIF: `total_sales / NULLIF(total_orders, 0)`. If total_orders is 0, NULLIF turns it into NULL, returning NULL instead of crashing the database!

Syntax Blueprint

SELECT
  name,
  COALESCE(discount_rate, 0.0) AS applied_discount,
  total_revenue / NULLIF(units_sold, 0) AS avg_unit_price
FROM products;

Use COALESCE for default fallbacks and NULLIF to prevent division-by-zero crashes.

Core Rules to Remember

COALESCE Portability: Standard ANSI SQL supported across Postgres, MySQL, SQLite, Oracle, and SQL Server.

Prevent Zero Division: `x / NULLIF(y, 0)` safely returns NULL instead of fatal runtime database errors.

Short-Circuit Evaluation: COALESCE stops evaluating arguments as soon as it encounters the first non-null entry.

Common Beginner Traps & How to Fix Them

Mistake:Dividing by raw columns `sales / count` without checking for zero.

Why it happens: A single 0 value in the divisor aborts the entire production query with "Division by zero".

How to fix: Always wrap the divisor with `NULLIF(divisor, 0)`.

Pro Tip: In user profiles, use `COALESCE(display_name, first_name || ' ' || last_name, email)` to provide clean fallback names.

Live Interactive Example

Hit Run Code to see it live

Safe Null Fallbacks and Division-by-Zero Protection

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: COALESCE selected the first valid phone number, and NULLIF safely returned NULL for Uptown Kiosk (0 customers).

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Provide Default with COALESCE

Select `name`, `COALESCE(nickname, name) AS display_name` from `users`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.