Mastering NULL in SQL: When to Use IS NULL and Why It Matters

When working with databases, the NULL value is a special and often misunderstood concept.

To test whether a column (e.g., USER_AGE) is NULL, the correct SQL syntax is: SELECT * FROM TABLE WHERE USER_AGE IS NULL Using = NULL is incorrect because NULL represents an unknown value, not a concrete value that can be compared with the equality operator.

In most databases, NULL differs from an empty string (""), although some systems like Oracle treat empty strings as NULL.

An empty string indicates a known, but empty, value.

NULL indicates an unknown value.

SQL’s WHERE clause can yield three possible results: true (return rows), false (return no rows), or NULL/unknown (also return no rows).

When applying NOT() to expressions involving NULL, the result remains NULL because the opposite of an unknown value is also unknown. SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL) Both WHERE NOT(1 = NULL) and WHERE 1 = NULL return no rows, even though they appear logically opposite.

NULL also affects the behavior of IN and NOT IN. For example, the query:

SELECT * FROM TABLE WHERE 1 IN (1, 2, 3, 4, NULL)

returns rows because 1 is present, but:

SELECT * FROM SOME_TABLE WHERE 5 NOT IN (1, 2, 3, 4, NULL)

returns no rows because the presence of NULL makes the result unknown (NULL), not false.

Understanding that NULL means “unknown” helps write correct SQL queries and avoid unexpected results.