Understanding NULL

NULL represents missing or unknown data — it is not zero, not an empty string, and not "false." It simply means "we don't have a value here."

Why NULL breaks normal logic

In SQL, comparing anything to NULL with = or <> doesn't return true or false — it returns unknown, which is treated as "not matched." That's why this query returns nothing, even for rows where manager_id is genuinely empty:

-- Wrong — this will never match anything
SELECT * FROM employees WHERE manager_id = NULL;

Always use IS NULL / IS NOT NULL instead:

SELECT * FROM employees WHERE manager_id IS NULL;

Result: first 5 of 6 rows (the order can differ on other engines unless you add ORDER BY)

id	first_name	last_name	email	department_id	job_title	salary	hire_date	manager_id
1	Ali	Sheikh	ali.sheikh1@csa-demo.local	1	Engineering Manager	188000	2024-10-16	NULL
2	Sara	Qureshi	sara.qureshi2@csa-demo.local	2	Business Development Executive	149000	2019-09-22	NULL
3	Ahmed	Qureshi	ahmed.qureshi3@csa-demo.local	3	SEO Specialist	101000	2023-12-28	NULL
4	Ayesha	Sheikh	ayesha.sheikh4@csa-demo.local	4	HR Manager	77000	2019-08-24	NULL
5	Bilal	Ahmed	bilal.ahmed5@csa-demo.local	5	Finance Manager	122000	2020-06-19	NULL

NULL in calculations

Any arithmetic involving NULL produces NULL. If a column allowed NULL prices, price * quantity would be NULL for those rows.

Replacing NULL with a default value

Use COALESCE (works on every engine) to substitute a fallback value:

SELECT first_name,
       COALESCE(manager_id, 0) AS manager_id_or_zero
FROM employees;

Result: first 5 of 26 rows (the order can differ on other engines unless you add ORDER BY)

first_name	manager_id_or_zero
Ali	0
Sara	0
Ahmed	0
Ayesha	0
Bilal	0

COALESCE returns the first non-null value from its argument list — you can pass more than two.

NULL and aggregate functions

Functions like COUNT, SUM, and AVG silently ignore NULL values rather than treating them as zero — an important detail we'll revisit in the aggregate functions article.

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.