Firebird Documentation Index → Firebird Null Guide → Searches |
If the search condition of a SELECT, UPDATE or
DELETE statement resolves to NULL
for a certain row,
the effect is the same as if it had been false
. Put another way: if the
search expression is NULL
, the condition is not met, and consequently the
row is not included in the output set (or is not updated/deleted).
The search condition or search expression is the WHERE clause minus the WHERE keyword itself.
Some examples (with the search condition in boldface):
SELECT Farmer, Cows FROM Farms WHERE Cows > 0 ORDER BY Cows
The above statement will return the rows for farmers that are known to possess at least
one cow. Farmers with an unknown (NULL
) number of cows will not be
included, because the expression “NULL > 0
” returns
NULL
.
SELECT Farmer, Cows FROM Farms WHERE NOT (Cows > 0) ORDER BY Cows
Now, it's tempting to think that this will return “all the other records”
from the Farms table, right? But it won't – not if the Cows column contains any
NULL
s. Remember that not(NULL)
is itself
NULL
. So for any row where Cows is NULL
,
“Cows > 0
” will be NULL
, and
“NOT (Cows > 0)
” will be NULL
as
well.
SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows + Sheep > 0
On the surface, this looks like a query returning all the farms that have at least one cow
and/or sheep (assuming that neither Cows nor Sheep can be a negative number). However, if farmer
Fred has 30 cows and an unknown number of sheep, the sum Cows + Sheep
becomes
NULL
, and the entire search expression boils down to “NULL
> 0
”, which is... you got it. So despite his 30 cows, our friend Fred won't
make it into the result set.
As a last example, we shall rewrite the previous statement so that it
will return any farm which has at least one animal of a known kind, even if
the other number is NULL
. To do that, we exploit the fact that
“NULL or true
” returns true
– one of the
rare occasions where a NULL
operand doesn't render the entire expression
NULL
:
SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows > 0 OR Sheep > 0
This time, Fred's thirty cows will make the first comparison true
,
while the sheep bit is still NULL
. So we have “true or
NULL
”, which is true
, and the row will be included in the
output set.
If your search condition contains one or more IN predicates, there
is the additional complication that some of the list elements (or subselect results) may be
NULL
. The implications of this are discussed in The IN() predicate.
Firebird Documentation Index → Firebird Null Guide → Searches |