NULL Values

Learning Focus

Use this lesson to handle NULL Values properly — one of the most common sources of bugs in SQL queries.

What is NULL?

NULL in SQL means unknown or missing. It is NOT zero and NOT an empty string.

DuckDB follows standard SQL NULL semantics:

• NULL = NULL → NULL (not true!)
• NULL + 5 → NULL
• NULL OR true → true
• NULL AND false → false

Detecting NULLs

-- Correct: IS NULL / IS NOT NULL
SELECT * FROM employees WHERE hire_date IS NULL;
SELECT * FROM employees WHERE hire_date IS NOT NULL;

-- Wrong: = NULL never matches anything
SELECT * FROM employees WHERE hire_date = NULL;  -- returns 0 rows!

COALESCE — Replace NULL with Default

-- Return first non-NULL value
SELECT name, COALESCE(manager_name, 'No Manager') AS manager
FROM employees;

-- Multiple fallbacks
SELECT COALESCE(preferred_name, full_name, email, 'Unknown') AS display_name;

NULLIF — Return NULL on Match

-- Return NULL if salary = 0 (avoid division by zero)
SELECT name, total_sales / NULLIF(months_active, 0) AS monthly_avg
FROM sales_data;

IFNULL and NVL

-- DuckDB supports IFNULL (alias for COALESCE with 2 args)
SELECT IFNULL(manager_name, 'No Manager') FROM employees;

Aggregations and NULL

-- NULL values are ignored by aggregate functions
SELECT AVG(salary) FROM employees;  -- ignores NULLs
SELECT COUNT(*)    FROM employees;  -- counts all rows (including NULLs)
SELECT COUNT(hire_date) FROM employees;  -- skips NULL hire_dates

Sorting NULLs

-- Explicit NULL placement (DuckDB default: NULLS LAST for ASC)
SELECT * FROM employees ORDER BY hire_date NULLS FIRST;
SELECT * FROM employees ORDER BY hire_date NULLS LAST;

Common Pitfalls

Pitfall	Consequence	Prevention
col = NULL instead of IS NULL	Always returns 0 rows	Always use IS NULL / IS NOT NULL
Arithmetic with NULLs	Whole expression becomes NULL	Wrap with COALESCE(col, 0)
Filtering out NULLs unintentionally	Missing data in results	Check for IS NULL cases explicitly

Quick Reference

WHERE col IS NULL
WHERE col IS NOT NULL
COALESCE(col, default_val)
NULLIF(col, 0)
IFNULL(col, fallback)
COUNT(col)       -- excludes NULLs
COUNT(*)         -- includes NULLs

What's Next

• Next: Filtering and Conditions - Advanced filtering techniques.
• Module Overview - Return to this module index.