NULL: the missing value

What NULL means

NULL is not zero, not an empty text, and not the word "NULL". It is a marker that says "we do not know this value" or "there is no value". Priya Nair has no email in SHOP; Sneha Patil was absent, so her marks are NULL. Zero marks would mean she wrote the exam and scored nothing, which is a very different fact.

Module 2 showed the three-valued logic: a comparison with NULL is neither true nor false but unknown, so = NULL never matches and you test with IS NULL. This topic is about living with NULL in calculations, and replacing it when you need a value.

NULL spreads

SELECT name || ' <' || email || '>' AS label FROM customers WHERE id IN (2, 3);
-- Ravi Kumar <ravi@example.com>
-- NULL                            <- Priya's whole label vanished

COALESCE: the first value that is not NULL

COALESCE(a, b, c, ...) checks its arguments from left to right and returns the first one that is not NULL. With two arguments it means "use a, or b if a is missing". It is standard SQL and works in every major database.

SELECT name, COALESCE(email, 'not given') AS email FROM customers;
-- Asha Verma  | asha@example.com
-- Priya Nair  | not given
-- Arjun Reddy | not given   ... 6 rows

SELECT name, marks, marks + 5 AS with_grace, COALESCE(marks, 0) + 5 AS grace_or_zero
FROM students WHERE id IN (4, 5);
-- Imran Khan  | 58   | 63   | 63
-- Sneha Patil | NULL | NULL | 5

NULLIF: make a value NULL on purpose

NULLIF(a, b) returns NULL when a equals b, otherwise a. Its main use is safe division. A campaign with 0 clicks cannot have a cost per click, and dividing by 0 is an error in PostgreSQL ("division by zero") and SQL Server ("Divide by zero error encountered"). NULLIF(clicks, 0) turns the 0 into NULL, and anything divided by NULL is NULL, so the query runs and that row shows "no value".

SELECT name, ROUND(spend * 1.0 / NULLIF(clicks, 0), 2) AS cost_per_click
FROM campaigns;
-- Diwali Sale    | 20.83
-- Monsoon Offer  | 26.67
-- New Year Kurta | NULL      <- 0 clicks, no crash
-- Holi Colours   | 17.78

SQLite and MySQL (with default settings) already return NULL for x / 0 instead of failing, so you will not see the error here. Write NULLIF anyway: the query then behaves the same on every database, and it tells the reader you thought about zero.

NULL in sorting, DISTINCT and comparisons

• Sorting: SQLite and MySQL put NULLs first in ascending order, PostgreSQL last. Use NULLS LAST, or sort by COALESCE(marks, -1), to control it (module 2).
• DISTINCT treats all NULLs as one group, so SELECT DISTINCT dept_id shows a single NULL.
• Filters: WHERE dept_id <> 1 skips Deepak, whose dept_id is NULL. To include him, write WHERE dept_id IS DISTINCT FROM 1 (standard SQL, PostgreSQL, SQLite 3.39+, SQL Server 2022) or SQLite's shorter dept_id IS NOT 1.
• Counting: COUNT(*) counts rows, COUNT(marks) counts only rows where marks is not NULL. Module 4 builds on this.

Common mistakes

• Testing with = NULL. Use IS NULL / IS NOT NULL.
• Replacing NULL with 0 everywhere. An unknown amount is not a zero amount; averages and totals change. Decide per report.
• Joining text pieces with || when one can be NULL, and losing the whole value. Wrap each optional piece in COALESCE.
• Dividing by a column that can be 0. Use NULLIF(column, 0).
• Using ISNULL(x, 0) in MySQL. There ISNULL takes one argument; use IFNULL or COALESCE.