Firebird Docset → Firebird Database Docs → 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 Docset → Firebird Database Docs → Firebird Null Guide → Searches |