Working with NULL
The most misunderstood concept in SQL — and how to handle it
NULL Means Unknown
NULL means "unknown or missing." It is not zero, not an empty string, and not false. A customer without a phone number has NULL — it doesn't mean their number is blank. Here's what makes NULL tricky:
NULL = NULL evaluates to NULL (not true!). You cannot use = or != to test for NULL. You must use IS NULL or IS NOT NULL — these are the only operators that work correctly with NULL values.5 + NULL → NULL. 'hello' || NULL → NULL. NULL > 10 → NULL. Any arithmetic or comparison involving NULL produces NULL. This is called three-valued logic: true, false, or unknown.COALESCE(column, default) returns the first non-NULL argument in the list. Example: COALESCE(phone, 'No phone') returns the phone number if it exists, or the fallback string when it's NULL.What NULL Really Means
NULL ≠ zero, NULL ≠ empty string
- NULL = unknown / missing / not applicable
- NULL is NOT a value — it's the absence of a value
- Any comparison with NULL yields NULL (not TRUE/FALSE)
- NULL propagates: 5 + NULL = NULL, 'hello' || NULL = NULL
The NULL Trap
The most common SQL bug
- WHERE col = NULL → always empty (use IS NULL)
- WHERE col != NULL → always empty (use IS NOT NULL)
- NULL = NULL → NULL (not TRUE!)
- NOT IN with NULLs → returns zero rows silently
NULL in Operations
How NULL propagates through expressions
- Arithmetic: 5 + NULL = NULL
- Concatenation: 'abc' || NULL = NULL
- Comparison: NULL > 10 = NULL
- Logical: TRUE AND NULL = NULL, TRUE OR NULL = TRUE
- Aggregates: COUNT(*) includes NULLs, COUNT(col) skips them
- COALESCE(col, fallback) replaces NULL with a default value
NULL-Safe Patterns
How to handle NULLs correctly
- COALESCE(col, 0) for safe arithmetic
- NULLIF(a, b) returns NULL if a = b
- IS NULL / IS NOT NULL for filtering
- IFNULL(col, default) — MySQL/SQLite shorthand
NULL Rules to Memorize
Never forget these
- Use IS NULL, never = NULL
- NULL in NOT IN = zero results
- COUNT(*) counts NULLs, COUNT(col) doesn't
- ORDER BY sorts NULLs to one end (DB-dependent)
- GROUP BY treats all NULLs as one group
IS NULLTrue when the value is missing/unknownIS NOT NULLTrue when the value existsCOALESCE(a, b)Returns a if not NULL, otherwise b| id | product_id | rating | comment |
|---|---|---|---|
| 7001 | P101 | 5 | Excellent mouse, very comfy |
| 7002 | P101 | 4 | NULL |
| 7003 | P101 | 3 | Good but battery drains fast |
| 7004 | P102 | 5 | Solid build, worth every rupee |
| 7005 | P102 | 4 | Spacious — fits two monitors |
| 7006 | P103 | 4 | NULL |
| 7007 | P104 | 5 | Just works, durable braided cable |
| 7008 | P105 | 5 | Great sound for the price |
| 7009 | P105 | 4 | Battery could be better |
| 7010 | P107 | 3 | NULL |
| 7011 | P108 | 5 | Good notebooks, smooth paper |
| 7012 | P109 | 5 | Premium feel, noise cancellation is real |
Worked Example
Find reviews that have no comment, and show a default message instead.
| id | rating | comment_display |
|---|---|---|
| 7002 | 4 | (no comment) |
| 7006 | 4 | (no comment) |
| 7010 | 3 | (no comment) |
Using = to check for NULL
This ALWAYS returns zero rows. NULL = NULL is not true — it evaluates to NULL, which is treated as false in WHERE.
Use IS NULL: WHERE comment IS NULL
Key Concepts
Pro Tip
NULL is the #1 source of silent SQL bugs. Queries that look correct can return wrong results because of unexpected NULL propagation.
When to Use
Finding missing email addresses, handling optional fields, replacing NULLs for display, dealing with incomplete data in reports.
Find all reviews that DO have a comment. Show the product_id, rating, and comment.