NULL Handling

Beginner

12 minutes

15 minutes

10 minutes

SELECT & Data Retrieval

WHERE Clause & Filtering

Understand what NULL means (unknown/missing value)

Master NULL comparison: IS NULL, IS NOT NULL (never = NULL)

Handle NULL in calculations with COALESCE and NULLIF

Understand NULL behavior in aggregate functions

Use NULL-safe operations in different databases

Prevent common NULL-related bugs in production

A customer database has optional fields: middle_name, phone, email. Queries must handle missing data correctly. Calculating total_compensation = salary + bonus fails when bonus is NULL (returns NULL, not salary). Reports show "N/A" instead of blank for missing values. Understanding NULL prevents silent data loss.

NULL is the #1 source of SQL bugs. Every developer encounters NULL issues: WHERE col = NULL returns nothing, NULL + 100 = NULL, concatenation breaks with NULL. Understanding NULL handling is essential for data integrity, correct calculations, and bug-free queries.

NULL represents unknown or missing data. It is NOT a value - it means "no value". NULL behaves differently than 0, empty string, or FALSE. Understanding NULL is critical for correct SQL queries.

**What is NULL?**

- NULL = "Unknown" or "Missing Value"

- NULL ≠ 0 (zero is a value)

- NULL ≠ '' (empty string is a value)

- NULL ≠ FALSE (false is a value)

**NULL Comparison:**

- NULL = NULL returns FALSE (not TRUE!)

- NULL != NULL returns FALSE (not TRUE!)

- Always use IS NULL or IS NOT NULL

- Never use = NULL or != NULL

**NULL in Calculations:**

- NULL + anything = NULL

- NULL * anything = NULL

- NULL || 'text' = NULL (concatenation)

- Use COALESCE to provide defaults

**NULL in Aggregate Functions:**

- COUNT(*): Counts all rows (includes NULL)

- COUNT(column): Counts non-NULL values

- SUM, AVG, MIN, MAX: Ignore NULL values

- NULL in GROUP BY: NULLs grouped together

**COALESCE Function:**

- COALESCE(val1, val2, val3): Returns first non-NULL value

- Example: COALESCE(bonus, 0) returns 0 if bonus is NULL

- Use for default values in calculations