• Any argument to FIRST and SKIP that is not an integer literal or an SQL parameter must be enclosed in parentheses. This implies that a subquery expression must be enclosed in two pairs of parentheses.

• SKIP 0 is allowed but totally pointless.

• FIRST 0 is also allowed and returns an empty set.

• Negative SKIP and/or FIRST values result in an error.

• If a SKIP lands past the end of the dataset, an empty set is returned.

• If the number of rows in the dataset (or the remainder left after a SKIP) is less than the value of the m argument supplied for FIRST, that smaller number of rows is returned. These are valid results, not error conditions.

  |DELETE FROM MYTABLE
  |  WHERE ID IN (SELECT FIRST 10 ID FROM MYTABLE)

The following query will return the first 10 names from the People table:

  |select first 10 id, name from People
  |  order by name asc

The following query will return everything but the first 10 names:

  |select skip 10 id, name from People
  |  order by name asc

And this one returns the last 10 rows. Notice the double parentheses:

  |select skip ((select count(*) - 10 from People))
  |  id, name from People
  |  order by name asc

This query returns rows 81 to 100 of the People table:

  |select first 20 skip 80 id, name from People
  |  order by name asc

See alsoROWS

When selecting from a single table or view, the FROM clause need not contain anything more than the name. An alias may be useful or even necessary if there are subqueries that refer to the main select statement (as they often do — subqueries like this are called correlated subqueries).

Examples

  |select id, name, sex, age from actors
  |where state = 'Ohio'

  |select * from birds
  |where type = 'flightless'
  |order by family, genus, species

  |select firstname,
  |  middlename,
  |  lastname,
  |  date_of_birth,
  |  (select name from schools s where p.school = s.id) schoolname
  |from pupils p
  |where year_started = '2012'
  |order by schoolname, date_of_birth

   |SELECT PEARS
   |FROM FRUIT;
   | 
   |SELECT FRUIT.PEARS
   |FROM FRUIT;
   | 
   |SELECT PEARS
   |FROM FRUIT F;
   | 
   |SELECT F.PEARS
   |FROM FRUIT F;

  |SELECT FRUIT.PEARS
  |FROM FRUIT F;

A selectable stored procedure is a procedure that:

• contains at least one output parameter, and

• utilizes the SUSPEND keyword so the caller can fetch the output rows one by one, just as when selecting from a table or view.

The output parameters of a selectable stored procedure correspond to the columns of a regular table.

Selecting from a stored procedure without input parameters is just like selecting from a table or view:

  |select * from suspicious_transactions
  |  where assignee = 'John'

Any required input parameters must be specified after the procedure name, enclosed in parentheses:

  |select name, az, alt from visible_stars('Brugge', current_date, '22:30')
  |  where alt >= 20
  |  order by az, alt

Values for optional parameters (that is, parameters for which default values have been defined) may be omitted or provided. However, if you provide them only partly, the parameters you omit must all be at the tail end.

Supposing that the procedure visible_stars from the previous example has two optional parameters: min_magn (numeric(3,1)) and spectral_class (varchar(12)), the following queries are all valid:

  |select name, az, alt
  |from visible_stars('Brugge', current_date, '22:30');
  | 
  |select name, az, alt
  |from visible_stars('Brugge', current_date, '22:30', 4.0);
  | 
  |select name, az, alt
  |from visible_stars('Brugge', current_date, '22:30', 4.0, 'G');

But this one isn’t, because there’s a hole in the parameter list:

  |select name, az, alt
  |from visible_stars('Brugge', current_date, '22:30', 'G');

An alias for a selectable stored procedure is specified after the parameter list:

  |select
  |  number,
  |  (select name from contestants c where c.number = gw.number)
  |from get_winners('#34517', 'AMS') gw

If you refer to an output parameter (column) by qualifying it with the full procedure name, the procedure alias should be omitted:

  |select
  |  number,
  |  (select name from contestants c where c.number = get_winners.number)
  |from get_winners('#34517', 'AMS')

See alsoStored Procedures, CREATE PROCEDURE

A derived table is a valid SELECT statement enclosed in parentheses, optionally followed by a table alias and/or column aliases. The result set of the statement acts as a virtual table which the enclosing statement can query.

Syntax

  |(<select-query>)
  |  [[AS] derived-table-alias]
  |  [(<derived-column-aliases>)]
  | 
  |<derived-column-aliases> := column-alias [, column-alias ...]

The set returned data set by this SELECT FROM (SELECT FROM..) style of statement is a virtual table that can be queried within the enclosing statement, as if it were a regular table or view.

Sample using a derived table

The derived table in the query below returns the list of table names in the database and the number of columns in each. A drill-down query on the derived table returns the counts of fields and the counts of tables having each field count:

   |SELECT
   |  FIELDCOUNT,
   |  COUNT(RELATION) AS NUM_TABLES
   |FROM (SELECT
   |        R.RDB$RELATION_NAME RELATION,
   |        COUNT(*) AS FIELDCOUNT
   |      FROM RDB$RELATIONS R
   |        JOIN RDB$RELATION_FIELDS RF
   |        ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
   |        GROUP BY RELATION)
   |GROUP BY FIELDCOUNT

A trivial example demonstrating how the alias of a derived table and the list of column aliases (both optional) can be used:

  |SELECT
  |  DBINFO.DESCR, DBINFO.DEF_CHARSET
  |FROM (SELECT *
  |      FROM RDB$DATABASE) DBINFO
  |        (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

A more useful example

Suppose we have a table COEFFS which contains the coefficients of a number of quadratic equations we have to solve. It has been defined like this:

  |create table coeffs (
  |  a double precision not null,
  |  b double precision not null,
  |  c double precision not null,
  |  constraint chk_a_not_zero check (a <> 0)
  |)

Depending on the values of a, b and c, each equation may have zero, one or two solutions. It is possible to find these solutions with a single-level query on table COEFFS, but the code will look rather messy and several values (like the discriminant) will have to be calculated multiple times per row. A derived table can help keep things clean here:

  |select
  |  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  |  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  |  from
  |    (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

If we want to show the coefficients next to the solutions (which may not be a bad idea), we can alter the query like this:

  |select
  |  a, b, c,
  |  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  |  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  |  from
  |    (select a, b, c, b*b - 4*a*c as D, 2*a as denom
  |     from coeffs)

Notice that whereas the first query used a column aliases list for the derived table, the second adds aliases internally where needed. Both methods work, as long as every column is guaranteed to have a name.

A common table expression or CTE is a more complex variant of the derived table, but it is also more powerful. A preamble, starting with the keyword WITH, defines one or more named CTE's, each with an optional column aliases list. The main query, which follows the preamble, can then access these CTE's as if they were regular tables or views. The CTE's go out of scope once the main query has run to completion.

For a full discussion of CTE's, please refer to the section Common Table Expressions (WITH …​ AS …​ SELECT).

The following is a rewrite of our derived table example as a CTE:

  |with vars (b, D, denom) as (
  |  select b, b*b - 4*a*c, 2*a from coeffs
  |)
  |select
  |  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  |  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  |from vars

Except for the fact that the calculations that have to be made first are now at the beginning, this isn’t a great improvement over the derived table version. But we can now also eliminate the double calculation of sqrt(D) for every row:

   |with vars (b, D, denom) as (
   |  select b, b*b - 4*a*c, 2*a from coeffs
   |),
   |vars2 (b, D, denom, sqrtD) as (
   |  select b, D, denom, iif (D >= 0, sqrt(D), null) from vars
   |)
   |select
   |  iif (D >= 0, (-b - sqrtD) / denom, null) sol_1,
   |  iif (D >  0, (-b + sqrtD) / denom, null) sol_2
   |from vars2

The code is a little more complicated now, but it might execute more efficiently (depending on what takes more time: executing the SQRT function or passing the values of b, D and denom through an extra CTE). Incidentally, we could have done the same with derived tables, but that would involve nesting.

See alsoCommon Table Expressions (WITH …​ AS …​ SELECT).

A join always combines data rows from two sets (usually referred to as the left set and the right set). By default, only rows that meet the join condition (i.e., that match at least one row in the other set when the join condition is applied) make it into the result set. This default type of join is called an inner join. Suppose we have the following two tables:

If we join these tables like this:

  |select *
  |  from A
  |  join B on A.id = B.code;

then the result set will be:

The first row of A has been joined with the second row of B because together they met the condition A.id = B.code. The other rows from the source tables have no match in the opposite set and are therefore not included in the join. Remember, this is an INNER join. We can make that fact explicit by writing:

  |select *
  |  from A
  |  inner join B on A.id = B.code;

However, since INNER is the default, this is rarely done.

It is perfectly possible that a row in the left set matches several rows from the right set or vice versa. In that case, all those combinations are included, and we can get results like:

Sometimes we want (or need) all the rows of one or both of the sources to appear in the joined set, regardless of whether they match a record in the other source. This is where outer joins come in. A LEFT outer join includes all the records from the left set, but only matching records from the right set. In a RIGHT outer join it’s the other way around. FULL outer joins include all the records from both sets. In all outer joins, the holes (the places where an included source record doesn’t have a match in the other set) are filled up with NULLs.

In order to make an outer join, you must specify LEFT, RIGHT or FULL, optionally followed by the keyword OUTER.

Below are the results of the various outer joins when applied to our original tables A and B:

  |select *
  |  from A
  |  left [outer] join B on A.id = B.code;

  |select *
  |  from A
  |  right [outer] join B on A.id = B.code

  |select *
  |  from A
  |  full [outer] join B on A.id = B.code

Qualified joins specify conditions for the combining of rows. This happens either explicitly in an ON clause or implicitly in a USING clause.

Syntax

  |<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>
  | 
  |<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
  | 
  |<join-condition> ::= ON <condition> | USING (<column-list>)

  |/* Select all Detroit customers who made a purchase
  |   in 2013, along with the purchase details: */
  |select * from customers c
  |  join sales s on s.cust_id = c.id
  |  where c.city = 'Detroit' and s.year = 2013;

  |/* Same as above, but include non-buying customers: */
  |select * from customers c
  |  left join sales s on s.cust_id = c.id
  |  where c.city = 'Detroit' and s.year = 2013;

  |/* For each man, select the women who are taller than he.
  |   Men for whom no such woman exists are not included. */
  |select m.fullname as man, f.fullname as woman
  |  from males m
  |  join females f on f.height > m.height;

  |/* Select all pupils with their class and mentor.
  |   Pupils without a mentor are also included.
  |   Pupils without a class are not included. */
  |select p.firstname, p.middlename, p.lastname,
  |       c.name, m.name
  |  from pupils p
  |  join classes c on c.id = p.class
  |  left join mentors m on m.id = p.mentor;

  |select * from flotsam f
  |  join jetsam j
  |  on f.sea = j.sea
  |  and f.ship = j.ship;

  |select * from flotsam
  |  join jetsam using (sea, ship)

  |select f.*, j.*
  |  from flotsam f
  |  join jetsam j using (sea, ship);

Taking the idea of the named columns join a step further, a natural join performs an automatic equi-join on all the columns that have the same name in the left and right table. The data types of these columns must be compatible.

Syntax

  |<natural-join> ::= NATURAL [<join-type>] JOIN <source>
  | 
  |<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Given these two tables:

  |create table TA (
  |  a bigint,
  |  s varchar(12),
  |  ins_date date
  |);

  |create table TB (
  |  a bigint,
  |  descr varchar(12),
  |  x float,
  |  ins_date date
  |);

a natural join on TA and TB would involve the columns a and ins_date, and the following two statements would have the same effect:

  |select * from TA
  |  natural join TB;

  |select * from TA
  |  join TB using (a, ins_date);

Like all joins, natural joins are inner joins by default, but you can turn them into outer joins by specifying LEFT, RIGHT or FULL before the JOIN keyword.

Caution: if there are no columns with the same name in the two source relations, a CROSS JOIN is performed. We’ll get to this type of join in a minute.

The = operator, which is explicitly used in many conditional joins and implicitly in named column joins and natural joins, only matches values to values. According to the SQL standard, NULL is not a value and hence two NULLs are neither equal nor unequal to one another. If you need NULLs to match each other in a join, use the IS NOT DISTINCT FROM operator. This operator returns true if the operands have the same value or if they are both NULL.

  |select *
  |  from A join B
  |  on A.id is not distinct from B.code;

Likewise, in the — extremely rare — cases where you want to join on inequality, use IS DISTINCT FROM, not <>, if you want NULL to be considered different from any value and two NULLs considered equal:

  |select *
  |  from A join B
  |  on A.id is distinct from B.code;

A cross join produces the full set product of the two data sources. This means that it successfully matches every row in the left source to every row in the right source.

Syntax

  |<cross-join> ::= {CROSS JOIN | ,} <source>

Use of the comma syntax is discouraged, and we recommend using the explicit join syntax.

Cross-joining two sets is equivalent to joining them on a tautology (a condition that is always true). The following two statements have the same effect:

  |select * from TA
  |  cross join TB;

  |select * from TA
  |  join TB on 1 = 1;

Cross joins are inner joins, because they only include matching records – it just so happens that every record matches! An outer cross join, if it existed, wouldn’t add anything to the result, because what outer joins add are non-matching records, and these don’t exist in cross joins.

Cross joins are seldom useful, except if you want to list all the possible combinations of two or more variables. Suppose you are selling a product that comes in different sizes, different colors and different materials. If these variables are each listed in a table of their own, this query would return all the combinations:

  |select m.name, s.size, c.name
  |  from materials m
  |  cross join sizes s
  |  cross join colors c;

Firebird rejects unqualified field names in a query if these field names exist in more than one dataset involved in a join. This is even true for inner equi-joins where the field name figures in the ON clause like this:

  |select a, b, c
  |  from TA
  |  join TB on TA.a = TB.a;

There is one exception to this rule: with named columns joins and natural joins, the unqualified field name of a column taking part in the matching process may be used legally and refers to the merged column of the same name. For named columns joins, these are the columns listed in the USING clause. For natural joins, they are the columns that have the same name in both relations. But please notice again that, especially in outer joins, plain colname isn’t always the same as left.colname or right.colname. Types may differ, and one of the qualified columns may be NULL while the other isn’t. In that case, the value in the merged, unqualified column may mask the fact that one of the source values is absent.

If a join is performed with a stored procedure that is not correlated with other data streams via input parameters, there are no oddities. If correlation is involved, an unpleasant quirk reveals itself. The problem is that the optimizer denies itself any way to determine the interrelationships of the input parameters of the procedure from the fields in the other streams:

  |SELECT *
  |FROM MY_TAB
  |JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

Here, the procedure will be executed before a single record has been retrieved from the table, MY_TAB. The isc_no_cur_rec error error (no current record for fetch operation) is raised, interrupting the execution.

The solution is to use syntax that specifies the join order explicitly:

  |SELECT *
  |FROM MY_TAB
  |LEFT JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

This forces the table to be read before the procedure and everything works correctly.

Just as a WHERE clause limits the rows in a dataset to those that meet the search condition, so the HAVING subclause imposes restrictions on the aggregated rows in a grouped set. HAVING is optional, and can only be used in conjunction with GROUP BY.

The condition(s) in the HAVING clause can refer to:

• Any aggregated column in the select list. This is the most widely used alternative.

• Any aggregated expression that is not in the select list, but allowed in the context of the query. This is sometimes useful too.

• Any column in the GROUP BY list. While legal, it is more efficient to filter on these non-aggregated data at an earlier stage: in the WHERE clause.

• Any expression whose value doesn’t depend on the contents of the dataset (like a constant or a context variable). This is valid but utterly pointless, because it will either suppress the entire set or leave it untouched, based on conditions that have nothing to do with the set itself.

A HAVING clause can not contain:

• Non-aggregated column expressions that are not in the GROUP BY list.

• Column positions. An integer in the HAVING clause is just an integer.

• Column aliases –- not even if they appear in the GROUP BY clause!

Examples

Building on our earlier examples, this could be used to skip small groups of students:

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having count(*) >= 5;

To select only groups that have a minimum age spread:

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having max(age) - min(age) > 1.2;

Notice that if you’re really interested in this information, you’d normally include min(age) and max(age) -– or the expression max(age) - min(age) –- in the select list as well!

To include only 3rd classes:

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having class starting with '3';

Better would be to move this condition to the WHERE clause:

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M' and class starting with '3'
  |  group by class;

The simplest plans consist of just a relation name followed by a retrieval method. For example, for an unsorted single-table select without a WHERE clause:

  |select * from students
  |  plan (students natural);

If there’s a WHERE or a HAVING clause, you can specify the index to be used for finding matches:

  |select * from students
  |  where class = '3C'
  |  plan (students index (ix_stud_class));

The INDEX directive is also used for join conditions (to be discussed a little later). It can contain a list of indexes, separated by commas.

ORDER specifies the index for sorting the set if an ORDER BY or GROUP BY clause is present:

  |select * from students
  |  plan (students order pk_students)
  |  order by id;

ORDER and INDEX can be combined:

  |select * from students
  |  where class >= '3'
  |  plan (students order pk_students index (ix_stud_class))
  |  order by id;

It is perfectly OK if ORDER and INDEX specify the same index:

  |select * from students
  |  where class >= '3'
  |  plan (students order ix_stud_class index (ix_stud_class))
  |  order by class;

For sorting sets when there’s no usable index available (or if you want to suppress its use), leave out ORDER and prepend the plan expression with SORT:

  |select * from students
  |  plan sort (students natural)
  |  order by name;

Or when an index is used for the search:

  |select * from students
  |  where class >= '3'
  |  plan sort (students index (ix_stud_class))
  |  order by name;

Notice that SORT, unlike ORDER, is outside the parentheses. This reflects the fact that the data rows are retrieved unordered and sorted afterwards by the engine.

When selecting from a view, specify the view and the table involved. For instance, if you have a view FRESHMEN that selects just the first-year students:

  |select * from freshmen
  |  plan (freshmen students natural);

Or, for instance:

  |select * from freshmen
  |  where id > 10
  |  plan sort (freshmen students index (pk_students))
  |  order by name desc;

When a join is made, you can specify the index which is to be used for matching. You must also use the JOIN directive on the two streams in the plan:

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan join (s natural, c index (pk_classes));

The same join, sorted on an indexed column:

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan join (s order pk_students, c index (pk_classes))
  |  order by s.id;

And on a non-indexed column:

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan sort (join (s natural, c index (pk_classes)))
  |  order by s.name;

With a search added:

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  where s.class <= '2'
  |  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  |  order by s.name;

As a left outer join:

  |select s.id, s.name, s.class, c.mentor
  |  from classes c
  |  left join students s on c.name = s.class
  |  where s.class <= '2'
  |  plan sort (join (c natural, s index (fk_student_class)))
  |  order by s.name;

If there is no index available to match the join criteria (or if you don’t want to use it), the plan must first sort both streams on their join column(s) and then merge them. This is achieved with the SORT directive (which we’ve already met) and MERGE instead of JOIN:

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  plan merge (sort (c natural), sort (s natural));

Adding an ORDER BY clause means the result of the merge must also be sorted:

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  plan sort (merge (sort (c natural), sort (s natural)))
  |  order by c.name, s.id;

Finally, we add a search condition on two indexable colums of table STUDENTS:

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  where s.id < 10 and s.class <= '2'
  |  plan sort (merge (sort (c natural),
  |                    sort (s index (pk_students, fk_student_class))))
  |  order by c.name, s.id;

As follows from the formal syntax definition, JOINs and MERGEs in the plan may combine more than two streams. Also, every plan expression may be used as a plan item in an encompassing plan. This means that plans of certain complicated queries may have various nesting levels.

Finally, instead of MERGE you may also write SORT MERGE. As this makes absolutely no difference and may create confusion with real SORT directives (the ones that do make a difference), it’s probably best to stick to plain MERGE.

  |MERGE (unsorted stream, unsorted stream)

The keyword ASCENDING, usually abbreviated to ASC, specifies a sort direction from lowest to highest. ASCENDING is the default sort direction.

The keyword DESCENDING, usually abbreviated to DESC, specifies a sort direction from highest to lowest.

Specifying ascending order for one column and the descending order for another is allowed.

The keyword COLLATE specifies the collation order for a string column if you need a collation that is different from the normal one for this column. The normal collation order will be either the default one for the database character set or one that has been set explicitly in the column’s definition.

The keyword NULLS defines where NULL in the associated column will fall in the sort order: NULLS FIRST places the rows with the NULL column above rows ordered by that column’s value; NULLS LAST places those rows after the ordered rows.

NULLS FIRST is the default.

The discrete queries contributing to a UNION cannot take an ORDER BY clause. The only option is to order the entire output, using one ORDER BY clause at the end of the overall query.

The simplest — and, in some cases, the only — method for specifying the sort order is by the ordinal column position. However, it is also valid to use the column names or aliases, from the first contributing query only.

The ASC/DESC and/or NULLS directives are available for this global set.

If discrete ordering within the contributing set is required, use of derived tables or common table expressions for those sets may be a solution.

Sorting the result set in ascending order, ordering by the RDB$CHARACTER_SET_ID, RDB$COLLATION_ID columns of the RDB$COLLATIONS table:

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY RDB$CHARACTER_SET_ID, RDB$COLLATION_ID;

The same, but sorting by the column aliases:

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY CHARSET_ID, COLL_ID;

Sorting the output data by the column position numbers:

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY 1, 2;

Sorting a SELECT * query by position numbers — possible, but nasty and not recommended:

  |SELECT *
  |FROM RDB$COLLATIONS
  |ORDER BY 3, 2;

Sorting by the second column in the BOOKS table:

  |SELECT
  |    BOOKS.*,
  |    FILMS.DIRECTOR
  |FROM BOOKS, FILMS
  |ORDER BY 2;

Sorting in descending order by the values of column PROCESS_TIME, with NULLs placed at the beginning of the set:

  |SELECT *
  |FROM MSG
  |ORDER BY PROCESS_TIME DESC NULLS FIRST;

Sorting the set obtained by a UNION of two queries. Results are sorted in descending order for the values in the second column, with NULLs at the end of the set; and in ascending order for the values of the first column with NULLs at the beginning.

  |SELECT
  |  DOC_NUMBER, DOC_DATE
  |FROM PAYORDER
  |UNION ALL
  |SELECT
  |  DOC_NUMBER, DOC_DATE
  |FROM BUDGORDER
  |ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST;

ROWS syntax cannot be mixed with FIRST/SKIP syntax in the same SELECT expression. Using the different syntaxes in different subqueries in the same statement is allowed.

When ROWS is used in a UNION query, the ROWS directive is applied to the unioned set and must be placed after the last SELECT statement.

If a need arises to limit the subsets returned by one or more SELECT statements inside UNION, there are a couple of options:

1. Use FIRST/SKIP syntax in these SELECT statements — bearing in mind that an ordering clause (ORDER BY) cannot be applied locally to the discrete queries, but only to the combined output.

2. Convert the queries to derived tables with their own ROWS clauses.

The following examples rewrite the examples used in the section about FIRST and SKIP, earlier in this chapter.

Retrieve the first ten names from the output of a sorted query on the PEOPLE table:

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 1 TO 10;

or its equivalent

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 10;

Return all records from the PEOPLE table except for the first 10 names:

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 11 TO (SELECT COUNT(*) FROM People);

And this query will return the last 10 records (pay attention to the parentheses):

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS (SELECT COUNT(*) - 9 FROM People)
  |TO (SELECT COUNT(*) FROM People);

This one will return rows 81-100 from the PEOPLE table:

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 81 TO 100;

If the FOR UPDATE sub-clause precedes the WITH LOCK sub-clause, buffered fetches are suppressed. Thus, the lock will be applied to each row, one by one, at the moment it is fetched. It becomes possible, then, that a lock which appeared to succeed when requested will nevertheless fail subsequently, when an attempt is made to fetch a row which has become locked by another transaction in the meantime.

See alsoFOR UPDATE [OF]

When an UPDATE statement tries to access a record that is locked by another transaction, it either raises an update conflict exception or waits for the locking transaction to finish, depending on TPB mode. Engine behaviour here is the same as if this record had already been modified by the locking transaction.

No special gdscodes are returned from conflicts involving pessimistic locks.

The engine guarantees that all records returned by an explicit lock statement are actually locked and do meet the search conditions specified in WHERE clause, as long as the search conditions do not depend on any other tables, via joins, subqueries, etc. It also guarantees that rows not meeting the search conditions will not be locked by the statement. It can not guarantee that there are no rows which, though meeting the search conditions, are not locked.

The engine locks rows at fetch time. This has important consequences if you lock several rows at once. Many access methods for Firebird databases default to fetching output in packets of a few hundred rows (buffered fetches). Most data access components cannot bring you the rows contained in the last-fetched packet, where an error occurred.

• Rolling back of an implicit or explicit savepoint releases record locks that were taken under that savepoint, but it doesn’t notify waiting transactions. Applications should not depend on this behaviour as it may get changed in the future.

• While explicit locks can be used to prevent and/or handle unusual update conflict errors, the volume of deadlock errors will grow unless you design your locking strategy carefully and control it rigorously.

• Most applications do not need explicit locks at all. The main purposes of explicit locks are:

  1. to prevent expensive handling of update conflict errors in heavily loaded applications, and

  2. to maintain integrity of objects mapped to a relational database in a clustered environment.

  If your use of explicit locking doesn’t fall in one of these two categories, then it’s the wrong way to do the task in Firebird.

• Explicit locking is an advanced feature; do not misuse it! While solutions for these kinds of problems may be very important for web sites handling thousands of concurrent writers, or for ERP/CRM systems operating in large corporations, most application programs do not need to work in such conditions.

1. Simple:

     |SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;
   

2. Multiple rows, one-by-one processing with DSQL cursor:

     |SELECT * FROM DOCUMENT WHERE PARENT_ID=?
     |  FOR UPDATE WITH LOCK;
   

A recursive (self-referencing) CTE is a UNION which must have at least one non-recursive member, called the anchor. The non-recursive member(s) must be placed before the recursive member(s). Recursive members are linked to each other and to their non-recursive neighbour by UNION ALL operators. The unions between non-recursive members may be of any type.

Recursive CTEs require the RECURSIVE keyword to be present right after WITH. Each recursive union member may reference itself only once, and it must do so in a FROM clause.

A great benefit of recursive CTEs is that they use far less memory and CPU cycles than an equivalent recursive stored procedure.

Execution Pattern

The execution pattern of a recursive CTE is as follows:

• The engine begins execution from a non-recursive member.

• For each row evaluated, it starts executing each recursive member one by one, using the current values from the outer row as parameters.

• If the currently executing instance of a recursive member produces no rows, execution loops back one level and gets the next row from the outer result set.

Example of recursive CTEs

   |WITH RECURSIVE DEPT_YEAR_BUDGET AS (
   |  SELECT
   |      FISCAL_YEAR,
   |      DEPT_NO,
   |      SUM(PROJECTED_BUDGET) BUDGET
   |  FROM PROJ_DEPT_BUDGET
   |  GROUP BY FISCAL_YEAR, DEPT_NO
   |),
   |DEPT_TREE AS (
   |  SELECT
   |      DEPT_NO,
   |      HEAD_DEPT,
   |      DEPARTMENT,
   |      CAST('' AS VARCHAR(255)) AS INDENT
   |  FROM DEPARTMENT
   |  WHERE HEAD_DEPT IS NULL
   |  UNION ALL
   |  SELECT
   |      D.DEPT_NO,
   |      D.HEAD_DEPT,
   |      D.DEPARTMENT,
   |      H.INDENT || ' '
   |  FROM DEPARTMENT D
   |    JOIN DEPT_TREE H ON H.HEAD_DEPT = D.DEPT_NO
   |)
   |SELECT
   |    D.DEPT_NO,
   |    D.INDENT || D.DEPARTMENT DEPARTMENT,
   |    DYB_2008.BUDGET AS BUDGET_08,
   |    DYB_2009.BUDGET AS BUDGET_09
   |FROM DEPT_TREE D
   |    LEFT JOIN DEPT_YEAR_BUDGET DYB_2008 ON
   |      (D.DEPT_NO = DYB_2008.DEPT_NO) AND
   |      (DYB_2008.FISCAL_YEAR = 2008)
   |    LEFT JOIN DEPT_YEAR_BUDGET DYB_2009 ON
   |      (D.DEPT_NO = DYB_2009.DEPT_NO) AND
   |      (DYB_2009.FISCAL_YEAR = 2009);

The next example returns the pedigree of a horse. The main difference is that recursion occurs simultaneously in two branches of the pedigree.

   |WITH RECURSIVE PEDIGREE (
   |  CODE_HORSE,
   |  CODE_FATHER,
   |  CODE_MOTHER,
   |  NAME,
   |  MARK,
   |  DEPTH)
   |AS (SELECT
   |      HORSE.CODE_HORSE,
   |      HORSE.CODE_FATHER,
   |      HORSE.CODE_MOTHER,
   |      HORSE.NAME,
   |      CAST('' AS VARCHAR(80)),
   |      0
   |    FROM
   |      HORSE
   |    WHERE
   |      HORSE.CODE_HORSE = :CODE_HORSE
   |    UNION ALL
   |    SELECT
   |      HORSE.CODE_HORSE,
   |      HORSE.CODE_FATHER,
   |      HORSE.CODE_MOTHER,
   |      HORSE.NAME,
   |      'F' || PEDIGREE.MARK,
   |      PEDIGREE.DEPTH + 1
   |    FROM
   |      HORSE
   |      JOIN PEDIGREE
   |        ON HORSE.CODE_HORSE = PEDIGREE.CODE_FATHER
   |    WHERE
   |      PEDIGREE.DEPTH < :MAX_DEPTH
   |    UNION ALL
   |    SELECT
   |      HORSE.CODE_HORSE,
   |      HORSE.CODE_FATHER,
   |      HORSE.CODE_MOTHER,
   |      HORSE.NAME,
   |      'M' || PEDIGREE.MARK,
   |      PEDIGREE.DEPTH + 1
   |    FROM
   |      HORSE
   |      JOIN PEDIGREE
   |        ON HORSE.CODE_HORSE = PEDIGREE.CODE_MOTHER
   |    WHERE
   |      PEDIGREE.DEPTH < :MAX_DEPTH
   |)
   |SELECT
   |  CODE_HORSE,
   |  NAME,
   |  MARK,
   |  DEPTH
   |FROM
   |  PEDIGREE