SELECT

Aggregate functions: Extended functionality

Table of Contents

Mixing aggregate functions from different contexts
Aggregate functions and GROUP BY items inside subqueries
Subqueries inside aggregate functions
Nesting aggregate function calls
Aggregate statements: Stricter HAVING and ORDER BY

Changed in: 1.5

Description: Several types of mixing and nesting aggregate functions are supported since Firebird 1.5. They will be discussed in the following subsections. To get the complete picture, also look at the SELECT :: GROUP BY sections.

Mixing aggregate functions from different contexts

Firebird 1.5 and up allow the use of aggregate functions from different contexts inside a single expression.

Example:

select
  r.rdb$relation_name as "Table name",
  ( select max(i.rdb$statistics) || ' (' || count(*) || ')'
    from rdb$relation_fields rf
    where rf.rdb$relation_name = r.rdb$relation_name
  ) as "Max. IndexSel (# fields)"
from
  rdb$relations r
  join rdb$indices i on (i.rdb$relation_name = r.rdb$relation_name)
group by r.rdb$relation_name
having max(i.rdb$statistics) > 0
order by 2

This admittedly rather contrived query shows, in the second column, the maximum index selectivity of any index defined on a table, followed by the table's field count between parentheses. Of course you would normally display the field count in a separate column, or in the column with the table name, but the purpose here is to demonstrate that you can combine aggregates from different contexts in a single expression.

Warning

Firebird 1.0 also executes this type of query, but gives the wrong results!

Aggregate functions and GROUP BY items inside subqueries

Since Firebird 1.5 it is possible to use aggregate functions and/or expressions contained in the GROUP BY clause inside a subquery.

Examples:

This query returns each table's ID and field count. The subquery refers to flds.rdb$relation_name, which is also a GROUP BY item:
select
  flds.rdb$relation_name as "Relation name",
  ( select rels.rdb$relation_id
    from rdb$relations rels
    where rels.rdb$relation_name = flds.rdb$relation_name
  ) as "ID",
  count(*) as "Fields"
from rdb$relation_fields flds
group by flds.rdb$relation_name
The next query shows the last field from each table and its 1-based position. It uses the aggregate function MAX in a subquery.
select
  flds.rdb$relation_name as "Table",
  ( select flds2.rdb$field_name
    from rdb$relation_fields flds2
    where
      flds2.rdb$relation_name = flds.rdb$relation_name
      and flds2.rdb$field_position = max(flds.rdb$field_position)
  ) as "Last field",
  max(flds.rdb$field_position) + 1 as "Last fieldpos"
from rdb$relation_fields flds
group by 1
The subquery also contains the GROUP BY item flds.rdb$relation_name, but that's not immediately obvious because in this case the GROUP BY clause uses the column number.

Subqueries inside aggregate functions

Using a singleton subselect inside (or as) an aggregate function argument is supported in Firebird 1.5 and up.

Example:

select
  r.rdb$relation_name as "Table",
  sum( (select count(*)
        from rdb$relation_fields rf
        where rf.rdb$relation_name = r.rdb$relation_name)
  ) as "Ind. x Fields"
from
  rdb$relations r
  join rdb$indices i
    on (i.rdb$relation_name = r.rdb$relation_name)
group by
  r.rdb$relation_name

Nesting aggregate function calls

Firebird 1.5 allows the indirect nesting of aggregate functions, provided that the inner function is from a lower SQL context. Direct nesting of aggregate function calls, as in “COUNT( MAX( price ) )”, is still forbidden and punishable by exception.

Example: See under Subqueries inside aggregate functions, where COUNT() is used inside a SUM().

Aggregate statements: Stricter HAVING and ORDER BY

Firebird 1.5 and above are stricter than previous versions about what can be included in the HAVING and ORDER BY clauses. If, in the context of an aggregate statement, an operand in a HAVING or ORDER BY item contains a column name, it is only accepted if one of the following is true:

The column name appears in an aggregate function call (e.g. “HAVING MAX(SALARY) > 10000”).
The operand equals or is based upon a non-aggregate column that appears in the GROUP BY list (by name or position).

“Is based upon” means that the operand need not be exactly the same as the column name. Suppose there's a non-aggregate column “STR” in the select list. Then it's OK to use expressions like “UPPER(STR)”, “STR || '!'” or “SUBSTRING(STR FROM 4 FOR 2)” in the HAVING clause – even if these expressions don't appear as such in the SELECT or GROUP BY list.

[AS] before relation alias

Added in: IB

Description: The keyword AS can optionally be placed before a relation alias, just as it can be placed before a column alias. This feature dates back to InterBase times, but wasn't documented in the IB Language Reference.

Syntax:

SELECT ... FROM <relation> [AS] alias

<relation>  ::=  A table, view, or selectable SP

Examples:

select order_no, total, fullname
  from orders as o join customers as c on o.cust_id = c.cust_id

select order_no, total, fullname
  from orders o join customers c on o.cust_id = c.cust_id

The two queries are fully equivalent.

COLLATE subclause for text BLOB columns

Added in: 2.0

Description: COLLATE subclauses are now also supported for text BLOBs.

Example:

select NameBlob from MyTable
  where NameBlob collate pt_br = 'João'

Common Table Expressions (“WITH ... AS ... SELECT”)

Table of Contents

Recursive CTEs

Available in: DSQL, PSQL

Added in: 2.1

Description: A common table expression or CTE can be described as a virtual table or view, defined in a preamble to a main query, and going out of scope after the main query's execution. The main query can reference any CTEs defined in the preamble as if they were regular tables or views. CTEs can be recursive, i.e. self-referencing, but they cannot be nested.

Syntax:

<cte-construct>  ::=  <cte-defs>
                      <main-query>

<cte-defs>       ::=  WITH [RECURSIVE] <cte> [, <cte> ...]

<cte>            ::=  name [(<column-list>)] AS (<cte-stmt>)

<column-list>    ::=  column-alias [, column-alias ...]

<cte-stmt>       ::=  any SELECT statement or UNION

<main-query>     ::=  the main SELECT statement, which can refer to the
                      CTEs defined in the preamble

Example:

with dept_year_budget as (
  select fiscal_year,
         dept_no,
         sum(projected_budget) as budget
  from proj_dept_budget
  group by fiscal_year, dept_no
)
select d.dept_no,
       d.department,
       dyb_2008.budget as budget_08,
       dyb_2009.budget as budget_09
from department 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
where exists (
  select * from proj_dept_budget b
  where d.dept_no = b.dept_no
)

Notes:

A CTE definition can contain any legal SELECT statement, as long as it doesn't have a “WITH...” preamble of its own (no nesting).
CTEs defined for the same main query can reference each other, but care should be taken to avoid loops.
CTEs can be referenced from anywhere in the main query.
Each CTE can be referenced multiple times in the main query, possibly with different aliases.
When enclosed in parentheses, CTE constructs can be used as subqueries in SELECT statements, but also in UPDATEs, MERGEs etc.

In PSQL, CTEs are also supported in FOR loop headers:

for with my_rivers as (select * from rivers where owner = 'me')
    select name, length from my_rivers into :rname, :rlen
do
begin
  ..
end

Recursive CTEs

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.

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 with a recursive CTE:

with recursive
  dept_year_budget as (
    select fiscal_year,
           dept_no,
           sum(projected_budget) as 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 d.head_dept = h.dept_no
  )
select d.dept_no,
       d.indent || d.department as 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

Notes on recursive CTEs:

Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM, COUNT, MAX etc) are not allowed in recursive union members.
A recursive reference cannot participate in an outer join.
The maximum recursion depth is 1024.

Derived tables (“SELECT FROM SELECT”)

Added in: 2.0

Description: A derived table is the result set of a SELECT query, used in an outer SELECT as if it were an ordinary table. Put otherwise, it is a subquery in the FROM clause.

Syntax:

(select-query)
   [[AS] derived-table-alias]
   [(<derived-column-aliases>)]

<derived-column-aliases>  :=  column-alias [, column-alias ...]

Examples:

The derived table in the query below (shown in boldface) contains all the relation names in the database followed by their field count. The outer SELECT produces, for each existing field count, the number of relations having that field count.
select fieldcount,
       count(relation) as num_tables
from   (select r.rdb$relation_name as 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 the use of a derived table alias and column aliases list (both are optional):
select dbinfo.descr,
       dbinfo.def_charset
from   (select * from rdb$database) dbinfo
         (descr, rel_id, sec_class, def_charset)

Notes:

Derived tables can be nested.
Derived tables can be unions and can be used in unions. They can contain aggregate functions, subselects and joins, and can themselves be used in aggregate functions, subselects and joins. They can also be or contain queries on selectable stored procedures. They can have WHERE, ORDER BY and GROUP BY clauses, FIRST, SKIP or ROWS directives, etc. etc.
Every column in a derived table must have a name. If it doesn't have one by nature (e.g. because it's a constant) it must either be given an alias in the usual way, or a column aliases list must be added to the derived table specification.
The column aliases list is optional, but if it is used it must be complete. That is: it must contain an alias for every column in the derived table.
The optimizer can handle a derived table very efficiently. However, if the derived table is involved in an inner join and contains a subquery, then no join order can be made.

FIRST and SKIP

Available in: DSQL, PSQL

Added in: 1.0

Changed in: 1.5

Better alternative: ROWS

Description: FIRST limits the output of a query to the first so-many rows. SKIP will suppress the given number of rows before starting to return output.

Tip

In Firebird 2.0 and up, use the SQL-compliant ROWS syntax instead.

Syntax:

SELECT [FIRST (<int-expr>)] [SKIP (<int-expr>)] <columns> FROM ...

<int-expr>  ::=  Any expression evaluating to an integer.
<columns>   ::=  The usual output column specifications.
Note

If <int-expr> is an integer literal or a query parameter, the “()” may be omitted. Subselects on the other hand require an extra pair of parentheses.

FIRST and SKIP are both optional. When used together as in “FIRST m SKIP n”, the n topmost rows of the output set are discarded and the first m rows of the remainder are returned.

SKIP 0 is allowed, but of course rather pointless. FIRST 0 is allowed in version 1.5 and up, where it returns an empty set. In 1.0.x, FIRST 0 causes an error. Negative SKIP and/or FIRST values always 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 after a SKIP) is less than the value given after FIRST, that smaller number of rows is returned. These are valid results, not error situations.

Examples:

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–100 of the People table:
select first 20 skip 80 id, name from People
  order by name asc

Two Gotchas with FIRST in subselects

This:
```
delete from MyTable where ID in (select first 10 ID from MyTable)
```
will delete all of the rows in the table. Ouch! The sub-select is evaluating each 10 candidate rows for deletion, deleting them, slipping forward 10 more... ad infinitum, until there are no rows left. Beware! Or better: use the ROWS syntax, available since Firebird 2.0.
Queries like:
```
...where F1 in (select first 5 F2 from Table2 order by 1 desc)
```
won't work as expected, because the optimization performed by the engine transforms the IN predicate to the correlated EXISTS predicate shown below. It's obvious that in this case FIRST N doesn't make any sense:
```
...where exists
   ( select first 5 F2 from Table2
     where Table2.F2 = Table1.F1
     order by 1 desc )
```

GROUP BY

Table of Contents

Grouping by alias, position and expressions

Description: GROUP BY merges rows that have the same combination of values and/or NULLs in the item list into a single row. Any aggregate functions in the select list are applied to each group individually instead of to the dataset as a whole.

Syntax:

SELECT ... FROM ...
   GROUP BY <item> [, <item> ...]
   ...

<item>  ::=  column-name [COLLATE collation-name]
               | column-alias
               | column-position
               | expression
Only non-negative integer literals will be interpreted as column positions. If they are outside the range from 1 to the number of columns, an error is raised. Integer values resulting from expressions or parameter substitutions are simply invariables and will be used as such in the grouping. They will have no effect though, as their value is the same for each row.

A GROUP BY item cannot be a reference to an aggregate function (including one that is buried inside an expression) from the same context.

The select list may not contain expressions that can have different values within a group. To avoid this, the rule of thumb is to include each non-aggregate item from the select list in the GROUP BY list (whether by copying, alias or position).

Note: If you group by a column position, the expression at that position is copied internally from the select list. If it concerns a subquery, that subquery will be executed at least twice.

Grouping by alias, position and expressions

Changed in: 1.0, 1.5, 2.0

Description: In addition to column names, Firebird 2 allows column aliases, column positions and arbitrary valid expressions as GROUP BY items.

Examples:

These three queries all achieve the same result:

select strlen(lastname) as len_name, count(*)
  from people
  group by len_name

select strlen(lastname) as len_name, count(*)
  from people
  group by 1

select strlen(lastname) as len_name, count(*)
  from people
  group by strlen(lastname)

History: Grouping by UDF results was added in Firebird 1. Grouping by column positions, CASE outcomes and a limited number of internal functions in Firebird 1.5. Firebird 2 added column aliases and expressions in general as valid GROUP BY items (“expressions in general” absorbing the UDF, CASE and internal functions lot).

HAVING: Stricter rules

Changed in: 1.5

Description: See Aggregate statements: Stricter HAVING and ORDER BY.

JOIN

Table of Contents

Ambiguous field names rejected
CROSS JOIN
Named columns JOIN
Natural JOIN

Ambiguous field names rejected

Changed in: 1.0

Description: InterBase 6 accepts and executes statements like the one below, which refers to an unqualified column name even though that name exists in both tables participating in the JOIN:

select buses.name, garages.name
  from buses join garages on buses.garage_id = garage.id
  where name = 'Phideaux III'

The results of such a query are unpredictable. Firebird Dialect 3 returns an error if there are ambiguous field names in JOIN statements. Dialect 1 gives a warning but will execute the query anyway.

CROSS JOIN

Added in: 2.0

Description: Firebird 2.0 and up support CROSS JOIN, which performs a full set multiplication on the tables involved. Previously you had to achieve this by joining on a tautology (a condition that is always true) or by using the comma syntax, now deprecated.

Syntax:

SELECT ...
   FROM <relation> CROSS JOIN <relation>
   ...

<relation>  ::=  {table | view | cte | (select_stmt)} [[AS] alias]
Note: If you use CROSS JOIN, you can't use ON.

Example:

select * from Men cross join Women
order by Men.age, Women.age

-- old syntax:
--   select * from Men join Women on 1 = 1
--   order by Men.age, Women.age

-- comma syntax:
--   select * from Men, Women
--   order by Men.age, Women.age

Named columns JOIN

Added in: 2.1

Description: A named columns join is an equi-join on the columns named in the USING clause. These columns must exist in both relations.

Syntax:

SELECT ...
   FROM <relation> [<join_type>] JOIN <relation>
   USING (colname [, colname ...])
   ...

<relation>   ::=  {table | view | cte | (select_stmt)} [[AS] alias]
<join_type>  ::=  INNER | {LEFT | RIGHT | FULL} [OUTER]

Example:

select *
  from books join shelves
  using (shelf, bookcase)

The equivalent in traditional syntax:

select *
  from books b join shelves s
  on b.shelf = s.shelf and b.bookcase = s.bookcase

Notes:

The columns in the USING clause can be selected without qualifier. Be aware, however, that doing so in outer joins doesn't always give the same result as selecting left.colname or right.colname. One of the latter may be NULL while the other isn't; plain colname always returns the non-NULL alternative in such cases.
SELECT * from a named columns join returns each USING column only once. In outer joins, such a column always contains the non-NULL alternative except for rows where the field is NULL in both tables.

Natural JOIN

Added in: 2.1

Description: A natural join is an automatic equi-join on all the columns that exist in both relations. If there are no common column names, a CROSS JOIN is produced.

Syntax:

SELECT ...
   FROM <relation> NATURAL [<join_type>] JOIN <relation>
   ...

<relation>   ::=  {table | view | cte | (select_stmt)} [[AS] alias]
<join_type>  ::=  INNER | {LEFT | RIGHT | FULL} [OUTER]

Example:

select * from Pupils natural left join Tutors
Assuming that the Pupils and Tutors tables have two field names in common: TUTOR and CLASS, the equivalent traditional syntax is:
select * from Pupils p left join Tutors t
  on p.tutor = t.tutor and p.class = t.class

Notes:

Common columns can be selected from a natural join without qualifier. Beware, however, that doing so in outer joins doesn't always gives the same result as selecting left.colname or right.colname. One of the latter may be NULL while the other isn't; plain colname always returns the non-NULL alternative in such cases.
SELECT * from a natural join returns each common column only once. In outer joins, such a column always contains the non-NULL alternative except for rows where the field is NULL in both tables.

ORDER BY

Table of Contents

Order by column alias
Ordering by column position causes * expansion
Ordering by expressions
NULLs placement
Stricter ordering rules with aggregate statements

Syntax:

SELECT ... FROM ...
   ...
   ORDER BY <ordering-item> [, <ordering-item> ...]

<ordering-item>  ::=  {col-name | col-alias | col-position | expression}
                         [COLLATE collation-name]
                         [ASC[ENDING] | DESC[ENDING]]
                         [NULLS {FIRST|LAST}]

Order by column alias

Added in: 2.0

Description: Firebird 2.0 and above support ordering by column alias.

Example:

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

Ordering by column position causes * expansion

Changed in: 2.0

Description: If you order by column position in a “SELECT *” query, the engine will now expand the * to determine the sort column(s).

Examples:

The following wasn't possible in pre-2.0 versions:
select * from rdb$collations
order by 3, 2
The following would sort the output set on Films.Director in previous versions. In Firebird 2 and up, it will sort on the second column of Books:
select Books.*, Films.Director from Books, Films
order by 2

Ordering by expressions

Added in: 1.5

Description: Firebird 1.5 introduced the possibility to use expressions as ordering items. Please note that expressions consisting of a single non-negative whole number will be interpreted as column positions and cause an exception if they're not in the range from 1 to the number of columns.

Example:

select x, y, note from Pairs
order by x+y desc

Note

The number of function or procedure invocations resulting from a sort based on a UDF or stored procedure is unpredictable, regardless whether the ordering is specified by the expression itself or by the column position number.

Notes:

The number of function or procedure invocations resulting from a sort based on a UDF or stored procedure is unpredictable, regardless whether the ordering is specified by the expression itself or by the column position number.
Only non-negative whole number literals are interpreted as column positions. A whole number resulting from an expression evaluation or parameter substitution is seen as an integer invariable and will lead to a dummy sort, since its value is the same for each row.

`NULL`s placement

Changed in: 1.5, 2.0

Description: Firebird 1.5 has introduced the per-column NULLS FIRST and NULLS LAST directives to specify where NULLs appear in the sorted column. Firebird 2.0 has changed the default placement of NULLs.

Unless overridden by NULLS FIRST or NULLS LAST, NULLs in ordered columns are placed as follows:

In Firebird 1.0 and 1.5: at the end of the sort, regardless whether the order is ascending or descending.
In Firebird 2.0 and up: at the start of ascending orderings and at the end of descending orderings.

See also the table below for an overview of the different versions.

Table 7.1. NULLs placement in ordered columns

Ordering	`NULL`s placement
Ordering	Firebird 1	Firebird 1.5	Firebird 2
order by Field [asc]	bottom	bottom	top
order by Field desc	bottom	bottom	bottom
order by Field [asc \| desc] nulls first	—	top	top
order by Field [asc \| desc] nulls last	—	bottom	bottom

Notes

Pre-existing databases may need a backup-restore cycle before they show the correct NULL ordering behaviour under Firebird 2.0 and up.
No index will be used on columns for which a non-default NULLS placement is chosen. In Firebird 1.5, that is the case with NULLS FIRST. In 2.0 and higher, with NULLS LAST on ascending and NULLS FIRST on descending sorts.

Examples:

select * from msg
  order by process_time desc nulls first

select * from document
  order by strlen(description) desc
  rows 10

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

Stricter ordering rules with aggregate statements

Changed in: 1.5

Description: See Aggregate statements: Stricter HAVING and ORDER BY.

PLAN

Table of Contents

Handling of user PLANs improved
ORDER with INDEX
PLAN must include all tables

Available in: DSQL, ESQL, PSQL

Description: Specifies a user plan for the data retrieval, overriding the plan that the optimizer would have generated automatically.

Syntax:

PLAN <plan_expr>

<plan_expr>   ::=  [JOIN | [SORT] [MERGE]] (<plan_item> [, <plan_item> ...])

<plan_item>   ::=  <basic_item> | <plan_expr>

<basic_item>  ::=  {table | alias}
                   {NATURAL
                    | INDEX (<indexlist>))
                    | ORDER index [INDEX (<indexlist>)]}

<indexlist>   ::=  index [, index ...]

Handling of user PLANs improved

Changed in: 2.0

Description: Firebird 2 has implemented the following improvements in the handling of user-specified PLANs:

Plan fragments are propagated to nested levels of joins, enabling manual optimization of complex outer joins.
User-supplied plans will be checked for correctness in outer joins.
Short-circuit optimization for user-supplied plans has been added.
A user-specified access path can be supplied for any SELECT-based statement or clause.

ORDER with INDEX

Changed in: 2.0

Description: A single plan item can now contain both an ORDER and an INDEX directive (in that order).

Example:

plan (MyTable order ix_myfield index (ix_this, ix_that))

PLAN must include all tables

Changed in: 2.0

Description: In Firebird 2 and up, a PLAN clause must handle all the tables in the query. Previous versions sometimes accepted incomplete plans, but this is no longer the case.

Relation alias makes real name unavailable

Changed in: 2.0

Description: If you give a table or view an alias in a Firebird 2.0 or above statement, you must use the alias, not the table name, if you want to qualify fields from that relation.

Examples:

Correct usage:

select pears from Fruit

select Fruit.pears from Fruit

select pears from Fruit F

select F.pears from Fruit F

No longer possible:

select Fruit.pears from Fruit F

ROWS

Available in: DSQL, PSQL

Added in: 2.0

Description: Limits the amount of rows returned by the SELECT statement to a specified number or range.

Syntax:

With a single SELECT:

SELECT <columns> FROM ...
   [WHERE ...]
   [ORDER BY ...]
   ROWS <m> [TO <n>]

<columns>  ::=  The usual output column specifications.
<m>, <n>   ::=  Any expression evaluating to an integer.

With a UNION:

SELECT [FIRST p] [SKIP q] <columns> FROM ...
   [WHERE ...]
   [ORDER BY ...]

UNION [ALL | DISTINCT]

SELECT [FIRST r] [SKIP s] <columns> FROM ...
   [WHERE ...]
   [ORDER BY ...]

ROWS <m> [TO <n>]

With a single argument m, the first m rows of the dataset are returned.

Points to note:

If m > the total number of rows in the dataset, the entire set is returned.
If m = 0, an empty set is returned.
If m < 0, an error is raised.

With two arguments m and n, rows m to n of the dataset are returned, inclusively. Row numbers are 1-based.

Points to note when using two arguments:

If m > the total number of rows in the dataset, an empty set is returned.
If m lies within the set but n doesn't, the rows from m to the end of the set are returned.
If m < 1 or n < 1, an error is raised.
If n = m-1, an empty set is returned.
If n < m-1, an error is raised.

The SQL-compliant ROWS syntax obviates the need for FIRST and SKIP, except in one case: a SKIP without FIRST, which returns the entire remainder of the set after skipping a given number of rows. (You can often “fake it” though, by supplying a second argument that you know to be bigger than the number of rows in the set.)

You cannot use ROWS together with FIRST and/or SKIP in a single SELECT statement, but is it valid to use one form in the top-level statement and the other in subselects, or to use the two syntaxes in different subselects.

When used with a UNION, the ROWS subclause applies to the UNION as a whole and must be placed after the last SELECT. If you want to limit the output of one or more individual SELECTs within the UNION, you have two options: either use FIRST/SKIP on those SELECT statements, or convert them to derived tables with ROWS clauses.

ROWS can also be used with the UPDATE and DELETE statements.

UNION

Table of Contents

UNIONs in subqueries
UNION DISTINCT

Available in: DSQL, ESQL, PSQL

UNIONs in subqueries

Changed in: 2.0

Description: UNIONs are now allowed in subqueries. This applies not only to column-level subqueries in a SELECT list, but also to subqueries in ANY|SOME, ALL and IN predicates, as well as the optional SELECT expression that feeds an INSERT.

Example:

select name, phone, hourly_rate from clowns
where hourly_rate < all
  (select hourly_rate from jugglers
     union
   select hourly_rate from acrobats)
order by hourly_rate

UNION DISTINCT

Added in: 2.0

Description: You can now use the optional DISTINCT keyword when defining a UNION. This will show duplicate rows only once instead of every time they occur in one of the tables. Since DISTINCT, being the opposite of ALL, is the default mode anyway, this doesn't add any new functionality.

Syntax:

SELECT (...) FROM (...)
UNION [DISTINCT | ALL]
SELECT (...) FROM (...)

Example:

select name, phone from translators
  union distinct
select name, phone from proofreaders
Translators who also work as proofreaders (a not uncommon combination) will show up only once in the result set, provided their phone number is the same in both tables. The same result would have been obtained without DISTINCT. With ALL, they would appear twice.

WITH LOCK

Available in: DSQL, PSQL

Added in: 1.5

Description: WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:

extremely small (ideally, a singleton), and
precisely controlled by the application code.

This is for experts only!

The need for a pessimistic lock in Firebird is very rare indeed and should be well understood before use of this extension is considered.

It is essential to understand the effects of transaction isolation and other transaction attributes before attempting to implement explicit locking in your application.

Syntax:

SELECT ... FROM single_table
   [WHERE ...]
   [FOR UPDATE [OF ...]]
   WITH LOCK

If the WITH LOCK clause succeeds, it will secure a lock on the selected rows and prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends.

If the FOR UPDATE clause is included, the lock will be applied to each row, one by one, as it is fetched into the server-side row cache. 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 becomes locked by another transaction.

WITH LOCK can only be used with a top-level, single-table SELECT statement. It is not available:

in a subquery specification;
for joined sets;
with the DISTINCT operator, a GROUP BY clause or any other aggregating operation;
with a view;
with the output of a selectable stored procedure;
with an external table.

A lengthier, more in-depth discussion of “SELECT ... WITH LOCK” is included in the Notes. It is a must-read for everybody who considers using this feature.

SELECT

Tip

Aggregate functions: Extended functionality

Mixing aggregate functions from different contexts

Warning

Aggregate functions and GROUP BY items inside subqueries

Subqueries inside aggregate functions

Nesting aggregate function calls

Aggregate statements: Stricter HAVING and ORDER BY

[AS] before relation alias

COLLATE subclause for text BLOB columns

Common Table Expressions (“WITH ... AS ... SELECT”)

Recursive CTEs

Derived tables (“SELECT FROM SELECT”)

FIRST and SKIP

Tip

Note

Two Gotchas with FIRST in subselects

GROUP BY

Grouping by alias, position and expressions

HAVING: Stricter rules

JOIN

Ambiguous field names rejected

CROSS JOIN

Named columns JOIN

Natural JOIN

ORDER BY

Order by column alias

Ordering by column position causes * expansion

Ordering by expressions

Note

NULLs placement

Notes

Stricter ordering rules with aggregate statements

PLAN

Handling of user PLANs improved

ORDER with INDEX

PLAN must include all tables

Relation alias makes real name unavailable

ROWS

UNION

UNIONs in subqueries

UNION DISTINCT

WITH LOCK

This is for experts only!

`NULL`s placement