Saturday, April 27, 2013

NULL Has No Equivalents



One aspect involving NULL in SQL often stumps people. SQL expressions are tri-valued, meaning every expression
can be true, false, or NULL. This affects all kinds of comparisons, operators, and logic as you've already seen.
But a nuance of this kind of logic is occasionally forgotten, so we'll repeat it explicitly.



NULL has no equivalents.
No other value is the same as NULL, not even other NULL values.


If you run the following query, can you guess your results?

select first_name, last_name
from hr.employees
where commission_pct = NULL;

The answer is no rows will be selected. Even though the values exists, so the COMMISSION_PCT = NULL criterion
will never find a match, and you will never see results from this query.
Always use IS NULL and IS NOT NULL to find or exclude your NULL values.

No comments: