FirebirdSQL logo

FIRST, SKIP and ROWS

The following two directives crash a Firebird 1.5.n or lower server if given a NULL argument.In Firebird 2, they treat NULL as the value 0:

  • FIRST

  • SKIP

This new Firebird 2 directive returns an empty set if any argument is NULL:

  • ROWS

In new code, use ROWS, not FIRST and SKIP.

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.