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;

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;

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.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;