FirebirdSQL logo

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.

Note

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

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)

With an empty list

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.

With a NULL test expression

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=ExprN

which, if A is NULL, boils down to

NULL or NULL or ... or NULL

which is NULL.

The nullness of the second predicate follows from the fact that “not (NULL)” equals NULL.