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 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.
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 [nullguidepredin].
