Firebird Documentation Index → Firebird Null Guide → Predicates |
Predicates are statements about objects that return a boolean result:
true
, false
or unknown
(=
NULL
). In computer code you typically find predicates in places where a
yes/no type of decision has to be taken. For Firebird SQL, that means in
WHERE, HAVING, CHECK,
CASE WHEN, IF and WHILE
clauses.
Comparisons such as “x > y” also return boolean results, but they are generally not called predicates, although this is mainly a matter of form. An expression like Greater( x, y ) that does exactly the same would immediately qualify as a predicate. (Mathematicians like predicates to have a name – such as “Greater” or just plain “G” – and a pair of parentheses to hold the arguments.)
Firebird supports the following SQL predicates: IN, ANY, SOME, ALL, EXISTS and SINGULAR.
It is also perfectly defensible to call “IS [NOT] NULL” and “IS [NOT] DISTINCT FROM” predicates, despite the absence of parentheses. But, predicates or not, they have already been introduced and won't be discussed in this section.
The IN predicate compares the expression on its left-hand side to a
number of expressions passed in the argument list and returns true
if a
match is found. NOT IN always returns the opposite of
IN. Some examples of its use are:
select RoomNo, Floor from Classrooms where Floor in (3, 4, 5)delete from Customers where upper(Name) in ('UNKNOWN', 'NN', '')if ( A not in (MyVar, MyVar + 1, YourVar, HisVar) ) then ...
The list can also be generated by a one-column subquery:
select ID, Name, Class from Students where ID in (select distinct LentTo from LibraryBooks)
If the list is empty (this is only possible with a subquery), IN
always returns false
and NOT IN always returns
true
, even if the test expression is NULL
. This
makes sense: even if a value is unknown, it is certain not to occur in an empty list.
If the list is not empty and the test expression – called “A” in the
examples below – is NULL
, the following predicates will always return
NULL
, regardless of the expressions in the list:
A IN ( Expr1, Expr2, ..., ExprN
)
A NOT IN ( Expr1, Expr2, ...,
ExprN
)
The first result can be understood by writing out the entire expression as a disjunction (OR-chain) of equality tests:
A=Expr1 or A=Expr2 or ... or A=Expr
N
which, if A is NULL
, boils down to
NULL
orNULL
or ... orNULL
which is NULL
.
The nullness of the second predicate follows from the fact that “not
(NULL
)” equals NULL
.
If A has a proper value but the list contains one or more NULL
expressions, things become a little more complicated:
If at least one of the expressions in the list has the same value as A:
“A IN( Expr1, Expr2, ...,
ExprN
)” returns true
“A NOT IN( Expr1, Expr2, ...,
ExprN
)” returns false
This is due to the fact that “true
or
NULL
” returns true
(see above). Or,
more general: a disjunction where at least one of the elements is
true
, returns true
even if some other elements
are NULL
. (Any false
s, if present, are not in
the way. In a disjunction, true
rules.)
If none of the expressions in the list have the same value as A:
“A IN( Expr1, Expr2, ...,
ExprN
)” returns NULL
“A NOT IN( Expr1, Expr2, ...,
ExprN
)” returns NULL
This is because “false
or
NULL
” returns NULL
. In generalised
form: a disjunction that has only false
and
NULL
elements, returns NULL
.
Needless to say, if neither A nor any list expression is NULL
,
the result is always as expected and can only be true
or
false
.
The table below shows all the possible results for IN and NOT IN. To use it properly, start with the first question in the left column. If the answer is No, move on to the next line. As soon as an answer is Yes, read the results from the second and third columns and you're done.
Table 3. Results for “A [NOT] IN (<list>)”
Conditions | Results | |
---|---|---|
IN() | NOT IN() | |
Is the list empty? | false |
true |
Else, is A NULL ?
|
NULL |
NULL |
Else, is at least one list element equal to A? | true |
false |
Else, is at least one list element NULL ?
|
NULL |
NULL |
Else (i.e. all list elements are non-NULL and unequal to
A )
|
false |
true |
In many contexts (e.g. within IF and WHERE
clauses), a NULL
result behalves like false
in
that the condition is not satisfied when the test expression is NULL
.
On the one hand this is convenient for cases where you might expect
false
but NULL
is returned: you simply won't
notice the difference. On the other hand, this may also lead you to expect
true
when the expression is inverted (using NOT)
and this is where you'll run into trouble. In that sense, the most “dangerous”
case in the above table is when you use an expression of the type “A NOT
IN (<list>)”, with A indeed not present in the list (so you'd expect
a clear true
result) but the list happens to contain one or more
NULL
s.
Be especially careful if you use NOT IN with a subselect instead of an explicit list, e.g.
A not in ( select Number from MyTable )
If A is not present in the Number column, the result is true
if
no Number is NULL
, but NULL
if the column does
contain a NULL
entry. Please be aware that even in a situation where
A is constant and its value is never contained in the Number column, the result of the
expression (and therefore your program flow) may still vary over time according to the
absence or presence of NULL
s in the column. Hours of debugging fun!
Of course you can avoid this particular problem simply by adding “where Number is
not NULL
” to the subselect.
All Firebird versions before 2.0 contain a bug that causes [NOT] IN to return the wrong result if an index is active on the subselect and one of the following conditions is true:
A is NULL
and the subselect doesn't return any
NULL
s, or
A is not NULL
and the subselect result set doesn't contain
A but does contain NULL
(s).
Please realise that an index may be active even if it has not been created explicitly, namely if a key is defined on A.
Example: Table TA has a column A with values { 3, 8 }. Table TB has a column B
containing { 2, 8, 1, NULL
}. The expressions:
A [not] in ( select B from TB )
should both return NULL
for A = 3, because of the
NULL
in B. But if B is indexed, IN returns
false
and NOT IN returns
true
. As a result, the query
select A from TA where A not in ( select B from TB )
returns a dataset with one record – containing the field with value 3 – while it should have returned an empty set. Other errors may also occur, e.g. if you use “NOT IN” in an IF, CASE or WHILE statement.
As an alternative to NOT IN, you can use
“<>
ALL”. The
ALL predicate will be introduced shortly.
The IN() predicate is often used in CHECK
constraints. In that context, NULL
expressions have a surprisingly
different effect in Firebird versions 2.0 and up. This will be discussed in the section
CHECK
constraints.
Firebird has two quantifiers that allow you to compare a value to the results of a subselect:
ALL returns true
if the comparison is true
for every element in the subselect.
ANY and SOME (full synonyms) return
true
if the comparison is true for at least one
element in the subselect.
With ANY, SOME and ALL you provide the comparison operator yourself. This makes it more flexible than IN, which only supports the (implicit) “=” operator. On the other hand, ANY, SOME and ALL only accept a subselect as an argument; you can't provide an explicit list, as with IN.
Valid operators are
=
,
!=
,
<
,
>
,
=<
,
=>
and all their synonyms. You can't use
LIKE, CONTAINING, IS DISTINCT
FROM, or any other operators.
Some usage examples:
select name, income from blacksmiths where income > any( select income from goldsmiths )
(returns blacksmiths who earn more than at least one goldsmith)
select name, town from blacksmiths where town != all( select distinct town from goldsmiths )
(returns blacksmiths who live in a goldsmithless town)
if ( GSIncome !> some( select income from blacksmiths ) ) then PoorGoldsmith = 1; else PoorGoldsmith = 0;
(sets PoorGoldsmith to 1 if at least one blacksmith's income is not less than the value of GSIncome)
If the subselect returns an empty set, ALL returns
true
and ANY|SOME return
false
, even if the left-hand side expression is
NULL
. This follows from the definitions and the rules of formal logic.
(Math-heads will already have noticed that ALL is equivalent to the
universal (“A”) quantifier and
ANY|SOME to the existential (“E”)
quantifier.)
For non-empty sets, you can write out “A <op>
ANY|SOME
(<subselect>
)” as
A
<op>
E1or
A
<op>
E2or
...
or
A
<op>
En
with <op>
the operator used and E1, E2 etc. the items
returned by the subquery.
Likewise, “A <op>
ALL
(<subselect>
)” is the same as
A
<op>
E1and
A
<op>
E2and
...
and
A
<op>
En
This should look familiar. The first writeout is equal to that of the
IN predicate, except that the operator may now be something other than
“=
”. The second is different but has the same general form.
We can now work out how nullness of A and/or nullness of subselect results affect the
outcome of ANY|SOME and ALL.
This is done in the same way as earlier with IN, so instead of
including all the steps here we will just present the result tables. Again, read the
questions in the left column from top to bottom. As soon as you answer a question with
“Yes”, read the result from the second column and you're done.
Table 4. Results for “A <op> ANY|SOME (<subselect>)”
Conditions | Result |
---|---|
ANY | SOME | |
Does the subselect return an empty set? | false |
Else, is A NULL ?
|
NULL |
Else, does at least one comparison return true ?
|
true |
Else, does at least one comparison return NULL ?
|
NULL |
Else (i.e. all comparisons return false )
|
false |
If you think these results look a lot like what we saw with IN(),
you're right: with the “=
” operator,
ANY is the same as IN. In the same way,
“<>
ALL” is equivalent to
NOT IN.
In versions before 2.0, “=
ANY”
suffers from the same bug as IN. Under the “right”
circumstances, this can lead to wrong results with expressions of the type
“NOT A = ANY( ... )”.
On the bright side, “<>
ALL” is not affected and will always return the right
result.
Table 5. Results for “A <op> ALL (<subselect>)”
Conditions | Result |
---|---|
ALL | |
Does the subselect return an empty set? | true |
Else, is A NULL ?
|
NULL |
Else, does at least one comparison return false ?
|
false |
Else, does at least one comparison return NULL ?
|
NULL |
Else (i.e. all comparisons return true )
|
true |
Although “<>
ALL” always
works as it should, ALL should nevertheless be considered broken in
all pre-2.0 versions of Firebird: with every operator other than
“<>
”, wrong results may be returned if an index is
active on the subselect – with or without NULL
s around.
Strictly speaking, the second question in both tables (“is A
NULL
?”) is redundant and can be dropped. If A is
NULL
, all the comparisons return NULL
, so that
situation will be caught a little later. And while we're at it, we could drop the first
question too: the “empty set” situation is just a special case of the final
“else”. The whole thing then once again boils down to
“true
beats NULL
beats
false
” in disjunctions
(ANY|SOME) and “false
beats NULL
beats true
” in conjunctions
(ALL).
The reason we included those questions is convenience: you can see if a set is empty
at a glance, and it's also easier to check if the left-hand side expression is
NULL
than to evaluate each and every comparison result. But do feel
free to skip them, or to skip just the second. Do not, however, skip
the first question and start with the second: this will lead to a wrong conclusion if the
set is empty!
The EXISTS and SINGULAR predicates return information about a subquery, usually a correlated subquery. You can use them in WHERE, HAVING, CHECK, CASE, IF and WHILE clauses (the latter two are only available in PSQL, Firebird's stored procedure and trigger language).
EXISTS tells you whether a subquery returns at least one row of data. Suppose you want a list of farmers who are also landowners. You could get one like this:
SELECT Farmer FROM Farms WHERE EXISTS (SELECT * FROM Landowners WHERE Landowners.Name = Farms.Farmer)
This query returns the names of all farmers who also figure in the Landowners table.
The EXISTS predicate returns true
if the result
set of the subselect contains at least one row. If it is empty, EXISTS
returns false
. EXISTS never returns
NULL
, because a result set always either has rows, or hasn't. Of course
the subselect's search condition may evolve to NULL
for certain rows,
but that doesn't cause any uncertainty: such a row won't be included in the subresult
set.
In reality, the subselect doesn't return a result set at all. The engine simply
steps through the Landowners records one by one and applies the search condition. If it
evaluates to true
, EXISTS returns
true
immediately and the remaining records aren't checked. If it
evaluates to false
or NULL
, the search
continues. If all the records have been searched and there hasn't been a single
true
result, EXISTS returns
false
.
NOT EXISTS always returns the opposite of
EXISTS: false
or true
, never
NULL
. NOT EXISTS returns
false
immediately if it gets a true
result on the
subquery's search condition. Before returning true
it must step through
the entire set.
SINGULAR is an InterBase/Firebird extension to the SQL standard.
It is often described as returning true
if exactly one row in the
subquery meets the search condition. By analogy with EXISTS this would
make you expect that SINGULAR too will only ever return
true
or false
. After all, a result set has either
exactly 1 row or a different number of rows. Unfortunately, all versions of Firebird up to
and including 2.0 have a bug that causes NULL
results in a number of
cases. The behaviour is pretty inconsistent, but at the same time fully reproducible. For
instance, on a column A containing (1, NULL
, 1), a
SINGULAR test with subselect “A=1
” returns
NULL
, but the same test on a column with (1, 1,
NULL
) returns false
. Notice that only the
insertion order is different here!
To make matters worse, all versions prior to 2.0 sometimes return
NULL
for NOT SINGULAR where
false
or true
is returned for
SINGULAR. In 2.0, this at least doesn't happen anymore: it's either
false
vs. true
or twice
NULL
.
The code has been fixed for Firebird 2.1; from that version onward SINGULAR will return:
false
if the search condition is never
true
(this includes the empty-set case);
true
if the search condition is true
for
exactly 1 row;
false
if the search condition is true
for more than 1 row.
Whether the other rows yield false
, NULL
or
a combination thereof, is irrelevant.
NOT SINGULAR will always return the opposite of SINGULAR (as is already the case in 2.0).
In the meantime, if there's any chance that the search condition
may evolve to NULL
for one or more rows, you should always add an
IS NOT NULL condition to your [NOT] SINGULAR
clauses, e.g. like this:
... SINGULAR( SELECT * from MyTable WHERE MyField > 38 AND MyField IS NOT NULL )
Firebird Documentation Index → Firebird Null Guide → Predicates |