<< Predicates | Firebird Null Guide | Sorts >>

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).

Note: 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 NULLs. 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.

Caution: 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.

See also:
Data retrieval

back to top of page
<< Predicates | Firebird Null Guide | Sorts >>