Understanding NULL Values in Databases

NULL is a special and often confusing value in relational databases; it represents an unknown or undefined value rather than an empty string.

When checking whether a column such as USER_AGE is NULL, the correct SQL syntax is WHERE USER_AGE IS NULL , not WHERE USER_AGE = NULL .

SQL treats NULL as "unknown", so any comparison with NULL yields an unknown result, which behaves like a third logical state besides true and false.

Most databases distinguish between an empty string ("")—a known, albeit empty, value—and NULL, which is unknown. Oracle is an exception: it automatically converts empty strings to NULL.

Three‑valued logic

In a WHERE clause a condition can evaluate to:

true – the row is returned;

false – the row is not returned;

NULL (unknown) – the row is also not returned.

Because NULL represents an unknown value, the logical NOT operator propagates the unknown: NOT NULL results in NULL.

Examples:

SELECT * FROM SOME_TABLE WHERE NOT (1 = 1);   -- evaluates to false, returns no rows
SELECT * FROM SOME_TABLE WHERE NOT (1 = 0);   -- evaluates to true, returns rows
SELECT * FROM SOME_TABLE WHERE NOT (1 = NULL); -- evaluates to NULL, returns no rows

NULL and NOT IN

When NULL appears in an IN list, the overall result can become unknown. For instance, WHERE 1 IN (1,2,3,4,NULL) returns true because 1 is present, but WHERE 5 NOT IN (1,2,3,4,NULL) yields NULL, so no rows are returned.

Understanding these nuances helps avoid unexpected empty result sets when working with NULL values in SQL queries.