SQL – NULL Functions
Handle NULL values elegantly using COALESCE, NULLIF, IFNULL, and other NULL-managing functions.
Table of Contents
SQL provides several functions specifically designed to handle the awkwardness of NULL values in real data. The most important is COALESCE() — it scans a list of expressions and returns the first non-NULL value. This is indispensable for providing “default” values in reports where some fields may be empty.
NULLIF(a, b) is the opposite — it returns NULL if the two values are equal, otherwise returns the first value. It is frequently used to avoid division-by-zero errors by converting zero denominators to NULL.
Key NULL functions
| Function | What it does |
|---|---|
| COALESCE(a,b,c) | First non-NULL value |
| NULLIF(a,b) | NULL if a=b, else a |
| IFNULL(col,default) | SQLite: a or default |
| ISNULL(col,default) | SQL Server version |
Try It Yourself — Interactive SQL Editor
Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.
Key Points
- COALESCE returns the first non-NULL in a list of arguments
- NULLIF(a,b) prevents division by zero: val / NULLIF(qty,0)
- IFNULL is SQLite-specific shorthand for COALESCE with one fallback
- Chain COALESCE across multiple columns to find the first available value
Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.
In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.