<< Internal functions and directives | Firebird Null Guide | Searches >>

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 as 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's certain not to occur in an empty list.

back to top of page

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.

back to top of page

With NULLs in the list

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

• 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

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.

back to top of page

IN() results

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.

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 NULLs.

Caution: 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 NULLs 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.

Bug alert: 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 NULLs, 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.

IN() in CHECK constraints

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.

back to top of page

The ANY, SOME and ALL quantifiers

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).

Result values

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> E1 or A <op> E2 or ... 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> E1 and A <op> E2 and ... 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.

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.

Bug alert (revisited): 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.

ALL bug: 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 NULLs around.

Note: 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!

back to top of page

EXISTS and SINGULAR

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

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.

Note: 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 evolves to true, EXISTS returns true immediately and the remaining records aren't checked. If it evolves 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.

back to top of page

SINGULAR

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 )

See also:
Data retrieval
NULL
NOT NULL

back to top of page
<< Internal functions and directives | Firebird Null Guide | Searches >>