SQL NULL Explained: How Missing Values Work and Why They Matter

Real datasets are rarely complete. A customer may not have provided a phone number. An employee may not have a department assigned yet. A measurement may have failed before the value was recorded. SQL represents those absent or unknown values with NULL.

SQL for Files gives you a practical place to learn this behavior. You can open the app in your browser, work through the Learn SQL lessons, and later inspect missing values in your own CSV, JSON, or Parquet files locally with DuckDB WASM.

Without NULL, every missing value would need to be disguised as something else. A missing salary might become 0. A missing department might become an empty string. A missing date might become a fake placeholder date. Those shortcuts can make analysis misleading.

• A salary of 0 means the known salary is zero; NULL means the salary is not known.
• An empty string means text is present but blank; NULL means no value was provided.
• A placeholder date can look real; NULL clearly marks that the date is missing.

The most important beginner mistake is trying to compare a column to NULL with an equals sign. It looks reasonable, but it does not work the way normal comparisons do.

-- This does not find missing departments
SELECT *
FROM employees
WHERE department = NULL;

To find missing values, use IS NULL. To find values that are present, use IS NOT NULL.

SELECT *
FROM employees
WHERE department IS NULL;

SELECT *
FROM employees
WHERE department IS NOT NULL;

NULL values show up again and again as you learn more SQL. Aggregates like SUM and AVG usually ignore NULL values. LEFT JOIN results use NULL when no matching row exists. Grouping and subtotal queries can also use NULL in ways that deserve careful reading.

• COUNT(*) counts rows, including rows that contain NULL values.
• COUNT(column_name) counts only rows where that specific column is not NULL.
• AVG(column_name) usually averages the known values and skips missing ones.

The third lesson in the Understanding Data chapter introduces NULL before you move into your first SQL queries. That timing is intentional: missing values are not an advanced edge case. They are part of everyday data work.

1. Open the related Missing Values with NULL lesson below.
2. Compare NULL with empty strings, zero, and ordinary text values.
3. Remember to use IS NULL and IS NOT NULL when you start filtering rows later.