Firebird Documentation Index → Firebird Null Guide → Predicates
Firebird Home Firebird Home Prev: Internal functions and directivesFirebird Documentation IndexUp: Firebird Null GuideNext: Searches

Predicates

The IN predicate
The ANY, SOME and ALL quantifiers
EXISTS and SINGULAR

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.

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.

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

    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 falses, 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.

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.

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

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.

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.

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.

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


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!

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.

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 )
Prev: Internal functions and directivesFirebird Documentation IndexUp: Firebird Null GuideNext: Searches
Firebird Documentation Index → Firebird Null Guide → Predicates