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 |