Querying
NULL and Missing Values
NULL represents the absence of a value, not zero, empty string, or false.
Overview
NULL represents the absence of a value, not zero, empty string, or false. It is a special marker indicating that a data value is missing, unknown, or not applicable. In SQL, NULL is not equal to any value, including another NULL. This three-valued logic (TRUE, FALSE, UNKNOWN) affects comparisons, arithmetic, and aggregate functions. Understanding NULL is critical for writing correct queries, especially when filtering, joining, or aggregating data. SQL-86 introduced the concept, and it remains a core part of the language. Vendor implementations generally follow the standard, but some differences exist in how NULLs are sorted or treated in constraints. For example, SELECT * FROM t WHERE col = NULL returns no rows because the comparison evaluates to UNKNOWN. Instead, use IS NULL or IS NOT NULL. NULLs can appear in any data type, and they propagate through expressions: any arithmetic operation involving NULL yields NULL. Aggregate functions like SUM, AVG, and COUNT ignore NULLs, except for COUNT(*). This behavior can lead to unexpected results if not carefully handled. Proper use of NULL requires explicit checks and an understanding of how it interacts with operators and functions.
Syntax and usage
To test for NULL, use the predicates IS NULL and IS NOT NULL. These are the only standard ways to compare a value to NULL. For example, SELECT * FROM employees WHERE manager_id IS NULL retrieves rows where the manager_id is missing. You cannot use = NULL or <> NULL; those expressions evaluate to UNKNOWN and return no rows. In WHERE clauses, rows with UNKNOWN are filtered out. In CHECK constraints, a condition that evaluates to UNKNOWN is considered satisfied, allowing NULLs unless explicitly prohibited. To treat NULL as a specific value, use functions like COALESCE, NULLIF, or CASE. COALESCE(col, 0) returns 0 if col is NULL. NULLIF(a, b) returns NULL if a equals b, else a. Vendor-specific functions include IFNULL (MySQL, SQLite), ISNULL (SQL Server), and NVL (Oracle). In ORDER BY, NULLs are sorted either first or last depending on the DBMS; the standard does not specify. Some systems support NULLS FIRST or NULLS LAST syntax. When inserting or updating, omitting a column or using the keyword NULL sets it to NULL, provided the column allows NULLs. Primary keys and columns with NOT NULL constraints reject NULLs.
How it works
SQL uses three-valued logic: every comparison can be TRUE, FALSE, or UNKNOWN. Any comparison with NULL yields UNKNOWN. Logical operators combine UNKNOWN as follows: TRUE AND UNKNOWN is UNKNOWN, FALSE AND UNKNOWN is FALSE, TRUE OR UNKNOWN is TRUE, FALSE OR UNKNOWN is UNKNOWN, and NOT UNKNOWN is UNKNOWN. In a WHERE clause, only rows where the condition is TRUE are returned; UNKNOWN and FALSE are both excluded. This means that WHERE col = 1 will not return rows where col is NULL, even though logically one might expect it to. Similarly, WHERE col <> 1 also excludes NULLs. To include NULLs, you must explicitly add OR col IS NULL. In joins, NULLs do not match each other. A join condition like a.id = b.id will not match if either side is NULL. This is why outer joins are needed to preserve rows with NULLs. Aggregate functions ignore NULLs: COUNT(col) counts only non-NULL values, while COUNT(*) counts all rows. SUM, AVG, MIN, and MAX also skip NULLs. If all values are NULL, SUM returns NULL, AVG returns NULL, and COUNT returns 0. GROUP BY treats all NULLs as a single group. DISTINCT also treats NULLs as equal for the purpose of deduplication.
Examples
Consider a table employees with columns id, name, and manager_id. To find employees without a manager: SELECT * FROM employees WHERE manager_id IS NULL. To find employees with a manager: SELECT * FROM employees WHERE manager_id IS NOT NULL. To replace NULL manager_id with 0: SELECT id, name, COALESCE(manager_id, 0) AS manager_id FROM employees. To count how many employees have a manager: SELECT COUNT(manager_id) FROM employees. To count all employees: SELECT COUNT(*) FROM employees. To group by manager_id and count: SELECT manager_id, COUNT(*) FROM employees GROUP BY manager_id. This will include a group for NULL manager_id. To join employees with their managers: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. Employees with NULL manager_id will have NULL for manager. To filter out rows where manager_id is NULL in a join: SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id. This inner join excludes employees without managers. To use NULLIF to avoid division by zero: SELECT 10 / NULLIF(0, 0) returns NULL. These examples illustrate common patterns for handling NULLs in queries.
Common pitfalls
A frequent mistake is using = NULL or <> NULL in comparisons. These expressions always evaluate to UNKNOWN and return no rows. Always use IS NULL or IS NOT NULL. Another pitfall is assuming that COUNT(column) counts all rows; it counts only non-NULL values. Use COUNT(*) for row counts. In WHERE clauses, negating a condition with NULLs can be tricky: WHERE col <> 1 excludes NULLs, so if you want to include them, you must write WHERE col <> 1 OR col IS NULL. In NOT IN subqueries, if the subquery returns any NULL, the entire NOT IN condition evaluates to UNKNOWN for all rows, returning no results. This is a common source of bugs. For example, SELECT * FROM t WHERE id NOT IN (SELECT id FROM s) returns no rows if s.id contains NULL. Use NOT EXISTS instead. In aggregate functions, SUM of an empty set returns NULL, not 0. Use COALESCE(SUM(col), 0) to get 0. In ORDER BY, NULLs may sort first or last depending on the DBMS, which can affect pagination. In GROUP BY, all NULLs are grouped together, which is usually desired but can be surprising. In CHECK constraints, a condition that evaluates to UNKNOWN is considered satisfied, so CHECK (col > 0) allows NULLs unless you add col IS NOT NULL.
Best practices
Always use IS NULL and IS NOT NULL for NULL comparisons. Avoid = NULL and <> NULL. When writing WHERE clauses, consider whether NULLs should be included or excluded. If you want to include them, explicitly add OR col IS NULL. Use COALESCE or CASE to provide default values for NULLs in calculations or output. For aggregate functions, be aware that they ignore NULLs; use COUNT(*) for row counts and COALESCE for sums. In joins, use outer joins to preserve rows with NULLs when needed. In NOT IN subqueries, ensure the subquery does not return NULLs, or use NOT EXISTS. Define columns as NOT NULL when appropriate to avoid unexpected NULLs. Use CHECK constraints to enforce that NULLs are not allowed in specific columns. When sorting, use NULLS FIRST or NULLS LAST if supported to control NULL placement. Document the meaning of NULL in your schema: does it mean unknown, not applicable, or missing? This helps avoid misinterpretation. Test queries with NULL data to ensure they behave as expected. Use IS DISTINCT FROM (if supported) for null-safe comparisons. In summary, treat NULL explicitly and consistently.
Frequently asked questions
Q: Why does SELECT * FROM t WHERE col = NULL return no rows? A: Because any comparison with NULL yields UNKNOWN, and WHERE only returns rows where the condition is TRUE. Use IS NULL instead. Q: How do I count rows with NULL values? A: Use SELECT COUNT(*) FROM t WHERE col IS NULL. Q: What is the difference between COUNT(*) and COUNT(col)? A: COUNT(*) counts all rows, while COUNT(col) counts only rows where col is not NULL. Q: Can I use NULL in a CHECK constraint? A: Yes, but a condition that evaluates to UNKNOWN is considered satisfied, so CHECK (col > 0) allows NULLs unless you add col IS NOT NULL. Q: How do I replace NULL with a default value? A: Use COALESCE(col, default) or vendor-specific functions like IFNULL or NVL. Q: Do NULLs match in joins? A: No, NULLs do not match each other in join conditions; use outer joins to include them.