Retrieves a slice of rows from an ordered set

FIRST m limits the output of a query to the first m rows. SKIP n will skip the first n rows of the result set before returning rows.

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

[fblangref50-dml-select-offsetfetch], ROWS

When a SELECT statement is executed, the result set is not sorted in any way. It often happens that rows appear to be sorted chronologically, simply because they are returned in the same order they were added to the table by INSERT statements. This is not something you should rely on: the order may change depending on the plan or updates to rows, etc. To specify an explicit sorting order for the set specification, an ORDER BY clause is used.

The ORDER BY consists of a comma-separated list of the columns or expressions on which the result data set should be sorted. The sort order can be specified by the name of the column — but only if the column was not previously aliased in the SELECT columns list. The alias must be used if it was used in the select list. The ordinal position number of the column in the SELECT column list, the alias given to the column in the SELECT list with the help of the AS keyword, or the number of the column in the SELECT list can be used without restriction.

The three forms of expressing the columns for the sort order can be mixed in the same ORDER BY clause. For instance, one column in the list can be specified by its name and another column can be specified by its number.

The keyword ASC — short for ASCENDING — specifies a sort direction from lowest to highest. ASC is the default sort direction.

The keyword DESC — short for DESCENDING — specifies a sort direction from highest to lowest.

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

Using the keyword COLLATE in a <value-expression> specifies the collation order to apply for a string column if you need a collation order that is different from the normal collation for this column. The normal collation order is defined by either the default collation for the database character set, or the collation 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.

Not-parenthesized query expressions contributing to a UNION cannot take an ORDER BY clause. You can order the entire output, using one ORDER BY clause at the end of the overall query, or use parenthesized query expressions, which do allow ORDER BY.

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 parenthesized query expressions, derived tables, or common table expressions for those sets.

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

The same, but sorting by the column aliases:

Sorting the output data by the column position numbers:

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

Sorting by the second column in the BOOKS table, or — if BOOKS has only one column — the FILMS.DIRECTOR column:

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

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.

Retrieves a slice of rows from an ordered set

ROWS limits the amount of rows returned by the SELECT statement to a specified number or range.

The ROWS clause also does the same job as the FIRST and SKIP clauses, but neither are SQL-compliant. Unlike FIRST and SKIP, and OFFSET and FETCH, the ROWS and TO clauses accept any type of integer expression as their arguments, without parentheses. Of course, parentheses may still be needed for nested evaluations inside the expression, and a subquery must always be enclosed in parentheses.

Calling ROWS m retrieves the first m records from the set specified.

Calling ROWS m TO n retrieves the rows from the set, starting at row m and ending after row n — the set is inclusive.

While ROWS is an alternative to the FIRST and SKIP syntax, there is one situation where the ROWS syntax does not provide the same behaviour: specifying SKIP n on its own returns the entire intermediate set, without the first n rows. The ROWS …​ TO syntax needs a little help to achieve this.

With the ROWS syntax, you need a ROWS clause in association with the TO clause and deliberately make the second (n) argument greater than the size of the intermediate data set. This is achieved by creating an expression for n that uses a subquery to retrieve the count of rows in the intermediate set and adds 1 to it, or use a literal with a sufficiently large value.

The ROWS clause can be used instead of the SQL-standard OFFSET/FETCH or non-standard FIRST/SKIP clauses, except the case where only OFFSET or SKIP is used, that is when the whole result set is returned except for skipping the specified number of rows from the beginning.

To implement this behaviour using ROWS, you must specify the TO clause with a value larger than the size of the returned result set.

ROWS syntax cannot be mixed with FIRST/SKIP or OFFSET/FETCH 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:

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:

or its equivalent

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

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

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

[fblangref50-dml-select-first-skip], [fblangref50-dml-select-offsetfetch]

Retrieves a slice of rows from an ordered set

The OFFSET and FETCH clauses are an SQL standard-compliant equivalent for FIRST/SKIP, and an alternative for ROWS. The OFFSET clause specifies the number of rows to skip. The FETCH clause specifies the number of rows to fetch.

When <offset-fetch-expression> is left out of the FETCH clause (e.g. FETCH FIRST ROW ONLY), one row will be fetched.

The choice between ROW or ROWS, or FIRST or NEXT in the clauses is just for aesthetic purposes (e.g. making the query more readable or grammatically correct). There is no difference between OFFSET 10 ROW or OFFSET 10 ROWS, or FETCH NEXT 10 ROWS ONLY or FETCH FIRST 10 ROWS ONLY.

As with SKIP and FIRST, OFFSET and FETCH clauses can be applied independently, in both top-level and nested query expressions.

The following examples rewrite the FIRST/SKIP examples and ROWS examples earlier in this chapter.

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

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

And this query will return the last 10 records. Contrary to FIRST/SKIP and ROWS we cannot use expressions (including sub-queries). To retrieve the last 10 rows, reverse the sort to the first (last) 10 rows, and then sort in the right order.

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

[fblangref50-dml-select-first-skip], [fblangref50-dml-select-rows]

FOR UPDATE does not do what its name suggests. Its only effect currently is to disable the pre-fetch buffer.

The OF sub-clause does not do anything at all, and is only provided for syntax compatibility with other database systems.

Applies limited pessimistic locking

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

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.

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

As the engine considers, in turn, each record falling under an explicit lock statement, it returns either the record version that is the most currently committed, regardless of database state when the statement was submitted, or an exception.

When the optional SKIP LOCKED clause is specified, records locked by a different transaction are skipped.

Wait behaviour and conflict reporting depend on the transaction parameters specified in the TPB block:

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. This can be avoided by also using SKIP LOCKED.

FOR 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 error codes are returned from conflicts involving pessimistic locks.

The engine guarantees that all records returned by an explicit lock statement are 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, when an error occurred.

The OPTIMIZE FOR clause can only occur on a top-level SELECT.

This feature allows the optimizer to consider another (hopefully better) plan if only a subset or rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time.

It can also be specified at the session level using the SET OPTIMIZE management statement.

The default behaviour can be specified globally using the OptimizeForFirstRows setting in firebird.conf or databases.conf.

Passes SELECT output into variables

PSQL

In PSQL the INTO clause is placed at the end of the SELECT statement.

In PSQL code (triggers, stored procedures and executable blocks), the results of a SELECT statement can be loaded row-by-row into local variables. It is often the only way to do anything with the returned values at all, unless an explicit or implicit cursor name is specified. The number, order and types of the variables must match the columns in the output row.

A “plain” SELECT statement can only be used in PSQL if it returns at most one row, i.e. if it is a singleton select. For multi-row selects, PSQL provides the FOR SELECT loop construct, discussed later in the PSQL chapter. PSQL also supports the DECLARE CURSOR statement, which binds a named cursor to a SELECT statement. The cursor can then be used to walk the result set.

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.

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 next example returns the pedigree of a horse. The main difference is that recursion occurs simultaneously in two branches of the pedigree.

The previous sections used incomplete or simplified fragments of the SELECT syntax. Following is the full syntax.

The columns list contains one or more comma-separated value expressions. Each expression provides a value for one output column. Alternatively, * (“star” or “all”) can be used to stand for all the columns of all relations in the FROM clause.

It is always valid to qualify a column name (or “*”) with the name or alias of the table, view or selectable SP to which it belongs, followed by a dot (‘.’). For example, relationname.columnname, relationname.*, alias.columnname, alias.*. Qualifying is required if the column name occurs in more than one relation taking part in a join. Qualifying “*” is required if it is not the only item in the column list.

The column list may optionally be preceded by one of the keywords DISTINCT or ALL:

A COLLATE clause of a value-expression will not change the appearance of the column as such. However, if the specified collation changes the case or accent sensitivity of the column, it may influence:

A simple SELECT using only column names:

A query featuring a concatenation expression and a function call in the columns list:

A query with two subselects:

The following query accomplishes the same as the previous one using joins instead of subselects:

This query uses a CASE construct to determine the correct title, e.g. when sending mail to a person:

Query using a window function, ranks employees by salary.

Querying a selectable stored procedure:

Selecting from columns of a derived table. A derived table is a parenthesized SELECT statement whose result set is used in an enclosing query as if it were a regular table or view. The derived table is shown in bold here:

Asking the time through a context variable (CURRENT_TIME):

For those not familiar with RDB$DATABASE: this is a system table that is present in all Firebird databases and is guaranteed to contain exactly one row. Although it wasn’t created for this purpose, it has become standard practice among Firebird programmers to select from this table if you want to select “from nothing”, i.e. if you need data that are not bound to a table or view, but can be derived from the expressions in the output columns alone. Another example is:

Finally, an example where you select some meaningful information from RDB$DATABASE itself:

As you may have guessed, this will give you the default character set of the database.

Functions, Aggregate Functions, Window Functions, Context Variables, CASE, Subqueries

The FROM clause specifies the source(s) from which the data are to be retrieved. In its simplest form, this is a single table or view. However, the source can also be a selectable stored procedure, a derived table, or a common table expression. Multiple sources can be combined using various types of joins.

This section focuses on single-source selects. Joins are discussed in a following section.

When selecting from a single table or view, the FROM clause requires nothing 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).

A selectable stored procedure is a procedure that:

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 like selecting from a table or view:

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

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:

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

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

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

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

The result set returned 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.

The keyword LATERAL marks a table as a lateral derived table. Lateral derived tables can reference tables (including other derived tables) that occur earlier in the FROM clause. See [fblangref50-dml-select-joins-lateral] for more information.

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

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

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:

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

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:

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 CTEs, each with an optional column aliases list. The main query, which follows the preamble, can then access these CTEs as if they were regular tables or views. The CTEs go out of scope once the main query has run to completion.

For a full discussion of CTEs, please refer to the section [fblangref50-dml-select-cte].

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

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. However, we can now also eliminate the double calculation of sqrt(D) for every row:

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.

[fblangref50-dml-select-cte].

Joins combine data from two sources into a single set. This is done on a row-by-row basis and usually involves checking a join condition to determine which rows should be merged and appear in the resulting dataset. There are several types (INNER, OUTER) and classes (qualified, natural, etc.) of joins, each with its own syntax and rules.

Since joins can be chained, the datasets involved in a join may themselves be joined sets.

A join 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:

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:

However, since INNER is the default, it is usually omitted.

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, even if they don’t 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. A 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.

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:

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

Most qualified joins have an ON clause, with an explicit condition that can be any valid Boolean expression, but usually involves a comparison between the two sources involved.

Often, the condition is an equality test (or a number of ANDed equality tests) using the “=” operator. Joins like these are called equi-joins. (The examples in the section on inner and outer joins were all equi-joins.)

Examples of joins with an explicit condition:

Equi-joins often compare columns that have the same name in both tables. If this is the case, we can also use the second type of qualified join: the named columns join.

Named columns joins have a USING clause which states only the column names. So instead of this:

we can also write:

which is considerably shorter. The result set is a little different though — at least when using “SELECT *”:

If you want all the columns in the result set of the named columns join, set up your query like this:

This will give you the same result set as the explicit-condition join.

For an OUTER named columns join, there’s an additional twist when using “SELECT *” or an unqualified column name from the USING list:

If a row from one source set doesn’t have a match in the other but must still be included because of the LEFT, RIGHT or FULL directive, the merged column in the joined set gets the non-NULL value. That is fair enough, but now you can’t tell whether this value came from the left set, the right set, or both. This can be especially deceiving when the value came from the right hand set, because “*” always shows combined columns in the left hand part — even in the case of a RIGHT join.

Whether this is a problem or not depends on the situation. If it is, use the “a.*, b.*” approach shown above, with a and b the names or aliases of the two sources. Or better yet, avoid “*” altogether in your serious queries and qualify all column names in joined sets. This has the additional benefit that it forces you to think about which data you want to retrieve and where from.

It is your responsibility to make sure the column names in the USING list are of compatible types between the two sources. If the types are compatible but not equal, the engine converts them to the type with the broadest range of values before comparing the values. This will also be the data type of the merged column that shows up in the result set if “SELECT *” or the unqualified column name is used. Qualified columns on the other hand will always retain their original data type.

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.

Given these two tables:

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:

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.

A cross join produces the full set product — or Cartesian 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.

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:

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:

In the SQL:89 standard, the tables involved in a join were specified as a comma-delimited list in the FROM clause (in other words, a cross join). The join conditions were then specified in the WHERE clause among other search terms. This type of join is called an implicit join.

An example of an implicit join:

Mixing explicit and implicit joins is not recommend, but is allowed. However, some types of mixing are not supported by Firebird.

For example, the following query will raise the error “Column does not belong to referenced table”

That is because the explicit join cannot see the TA table. However, the next query will complete without error, since the restriction is not violated.

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.

Likewise, when 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:

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:

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.

A derived table defined with the LATERAL keyword is called a lateral derived table. If a derived table is defined as lateral, then it is allowed to refer to other tables in the same FROM clause, but only those declared before it in the FROM clause.

The WHERE clause serves to limit the rows returned to the ones that the caller is interested in. The condition following the keyword WHERE can be as simple as a check like “AMOUNT = 3” or it can be a multilayered, convoluted expression containing subselects, predicates, function calls, mathematical and logical operators, context variables and more.

The condition in the WHERE clause is often called the search condition, the search expression or simply the search.

In DSQL and ESQL, the search condition may contain parameters. This is useful if a query has to be repeated a number of times with different input values. In the SQL string as it is passed to the server, question marks are used as placeholders for the parameters. These question marks are called positional parameters because they can only be told apart by their position in the string. Connectivity libraries often support named parameters of the form :id, :amount, :a etc. These are more user-friendly; the library takes care of translating the named parameters to positional parameters before passing the statement to the server.

The search condition may also contain local (PSQL) or host (ESQL) variable names, preceded by a colon.

Only those rows for which the search condition evaluates to TRUE are included in the result set. Be careful with possible NULL outcomes: if you negate a NULL expression with NOT, the result will still be NULL and the row will not pass. This is demonstrated in one of the examples below.

The following example shows what can happen if the search condition evaluates to NULL.

Suppose you have a table listing children’s names and the number of marbles they possess. At a certain moment, the table contains this data:

First, please notice the difference between NULL and 0: Fritz is known to have no marbles at all, Chris’s and Hadassah’s marble counts are unknown.

Now, if you issue this SQL statement:

you will get the names Anita, Bob E., Eve and Gerry. These children all have more than 10 marbles.

If you negate the expression:

it’s the turn of Deirdre, Fritz and Isaac to fill the list. Chris and Hadassah are not included, because they aren’t known to have ten or fewer marbles. Should you change that last query to:

the result will still be the same, because the expression NULL <= 10 yields UNKNOWN. This is not the same as TRUE, so Chris and Hadassah are not listed. If you want them listed with the “poor” children, change the query to:

Now the search condition becomes true for Chris and Hadassah, because “marbles is null” obviously returns TRUE in their case. In fact, the search condition cannot be NULL for anybody now.

Lastly, two examples of SELECT queries with parameters in the search. It depends on the application how you should define query parameters and even if it is possible at all. Notice that queries like these cannot be executed immediately: they have to be prepared first. Once a parameterized query has been prepared, the user (or calling code) can supply values for the parameters and have it executed many times, entering new values before every call. How the values are entered and the execution started is up to the application. In a GUI environment, the user typically types the parameter values in one or more text boxes and then clicks an “Execute”, “Run” or “Refresh” button.

The last query cannot be passed directly to the engine; the application must convert it to the other format first, mapping named parameters to positional parameters.

GROUP BY merges output rows that have the same combination of values in its item list into a single row. Aggregate functions in the select list are applied to each group individually instead of to the dataset as a whole.

If the select list only contains aggregate columns or, more generally, columns whose values don’t depend on individual rows in the underlying set, GROUP BY is optional. When omitted, the final result set consists of a single row (provided that at least one aggregated column is present).

If the select list contains both aggregate columns and columns whose values may vary per row, the GROUP BY clause becomes mandatory.

A general rule of thumb is that every non-aggregate item in the SELECT list must also be in the GROUP BY list. You can do this in three ways:

In addition to the required items, the grouping list may also contain:

When the select list contains only aggregate columns, GROUP BY is not mandatory:

This will return a single row listing the number of male students and their average age. Adding expressions that don’t depend on values in individual rows of table STUDENTS doesn’t change that:

The row will now have an extra column showing the current date, but other than that, nothing fundamental has changed. A GROUP BY clause is still not required.

However, in both the above examples it is allowed. This is perfectly valid:

This will return a row for each class that has boys in it, listing the number of boys and their average age in that particular class. (If you also leave the current_date field in, this value will be repeated on every row, which is not very exciting.)

The above query has a major drawback though: it gives you information about the different classes, but it doesn’t tell you which row applies to which class. To get that extra bit of information, add the non-aggregate column CLASS to the select list:

Now we have a useful query. Notice that the addition of column CLASS also makes the GROUP BY clause mandatory. We can’t drop that clause anymore, unless we also remove CLASS from the column list.

The output of our last query may look something like this:

The headings “COUNT” and “AVG” are not very informative. In a simple case like this, you might get away with that, but in general you should give aggregate columns a meaningful name by aliasing them:

Adding more non-aggregate (or, row-dependent) columns requires adding them to the GROUP BY clause too. For instance, you might want to see the above information for girls as well; and you may also want to differentiate between boarding and day students:

This may give you the following result:

Each row in the result set corresponds to one particular combination of the columns CLASS, SEX and BOARDING_TYPE. The aggregate results — number and average age — are given for each of these groups individually. In a query like this, you don’t see a total for boys as a whole, or day students as a whole. That’s the tradeoff: the more non-aggregate columns you add, the more you can pinpoint specific groups, but the more you also lose sight of the general picture. Of course, you can still obtain the “coarser” aggregates through separate queries.

Just as a WHERE clause limits the rows in a dataset to those that meet the search condition, so the HAVING sub-clause 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:

A HAVING clause can not contain:

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

To select only groups that have a minimum age spread:

Notice that if you’re interested in this information, you’ll likely also include min(age) and max(age) — or the expression “max(age) - min(age)”.

To include only 3rd classes:

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

The WINDOW clause defines one or more named windows that can be referenced by window functions in the current query specification.

In a query with multiple SELECT and WINDOW clauses (for example, with subqueries), the scope of the `new_window_name_ is confined to its query context. That means a window name from an inner context cannot be used in an outer context, nor vice versa. However, the same window name can be used independently in different contexts, though to avoid confusion it might be better to avoid this.

For more information, see [fblangref50-windowfuncs].

The PLAN clause enables the user to submit a data retrieval plan, thus overriding the plan that the optimizer would have generated automatically.

Every time a user submits a query to the Firebird engine, the optimizer computes a data retrieval strategy. Most Firebird clients can make this retrieval plan visible to the user. In Firebird’s own isql utility, this is done with the command SET PLAN ON. If you are only interested in looking at query plans, SET PLANONLY ON will show the plan without executing the query. Use SET PLANONLY OFF to execute the query and show the plan.

In most situations, you can trust that Firebird will select the optimal query plan for you. However, if you have complicated queries that seem to be underperforming, it may be worth your while to examine the plan and see if you can improve on it.

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

Advanced plan:

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

Advanced plan:

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:

Advanced plan:

ORDER and INDEX can be combined:

Advanced plan:

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

Advanced plan:

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:

Advanced plan:

Or when an index is used for the search:

Advanced plan:

Notice that SORT, unlike ORDER, is outside the parentheses. This reflects the fact that the data rows are retrieved unordered and sorted afterward 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 the first-year students:

Advanced plan:

Or, for instance:

Advanced plan:

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:

Advanced plan:

The same join, sorted on an indexed column:

Advanced plan:

And on a non-indexed column:

Advanced plan:

With a search condition added:

Advanced plan:

As a left outer join:

Advanced plan:

If there are no indices available to match the join condition (or if you don’t want to use it), then it is possible connect the streams using HASH or MERGE method.

To connect using the HASH method in the plan, the HASH directive is used instead of the JOIN directive. In this case, the smaller (secondary) stream is materialized completely into an internal buffer. While reading this secondary stream, a hash function is applied and a pair {hash, pointer to buffer} is written to a hash table. Then the primary stream is read and its hash key is tested against the hash table.

Advanced plan:

For a MERGE join, the plan must first sort both streams on their join column(s) and then merge. This is achieved with the SORT directive (which we’ve already seen) and MERGE instead of JOIN:

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

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

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 no difference and may create confusion with “real” SORT directives (the ones that do make a difference), it’s best to stick to plain MERGE.

In addition to the plan for the main query, you can specify a plan for each subquery. For example, the following query with multiple plans will work:

The UNION clause concatenates two or more datasets, thus increasing the number of rows but not the number of columns. Datasets taking part in a UNION must have the same number of columns, and columns at corresponding positions must be of the same type.

By default, a union suppresses duplicate rows. UNION ALL shows all rows, including any duplicates. The optional DISTINCT keyword makes the default behaviour explicit.

Unions take their column names from the first select query. If you want to alias union columns, do so in the column list of the topmost SELECT. Aliases in other participating selects are allowed and may even be useful, but will not propagate to the union level.

If a union has an ORDER BY clause, the only allowed sort items are integer literals indicating 1-based column positions, optionally followed by an ASC/DESC and/or a NULLS {FIRST | LAST} directive. This also implies that you cannot order a union by anything that isn’t a column in the union. (You can, however, wrap it in a derived table, which gives you back all the usual sort options.)

Unions are allowed in subqueries of any kind and can themselves contain subqueries. They can also contain joins, and can take part in a join when wrapped in a derived table.

This query presents information from different music collections in one dataset using unions:

If id, title, artist and length are the only fields in the tables involved, the query can also be written as:

Qualifying the “stars” is necessary here because they are not the only item in the column list. Notice how the “c” aliases in the first and third select do not conflict with each other: their scopes are not union-wide but apply only to their respective select queries.

The next query retrieves names and phone numbers from translators and proofreaders. Translators who also work as proofreaders will show up only once in the result set, provided their phone number is the same in both tables. The same result can be obtained without DISTINCT. With ALL, these people would appear twice.

A UNION within a subquery:

Using parenthesized query expressions to show the employees with the highest and lowest salaries:

The VALUES list must provide a value for every column in the column list, in the same order and of the correct type. The column list need not specify every column in the target but, if the column list is absent, the engine requires a value for every column in the table or view (computed columns excluded).

The expression DEFAULT allows a column to be specified in the column list, but instructs Firebird to use the default value (either NULL or the value specified in the DEFAULT clause of the column definition). For identity columns, specifying DEFAULT will generate the identity value. It is possible to include calculated columns in the column list and specifying DEFAULT as the column value.

For this method of inserting, the output columns of the SELECT statement (or <query-expression>) must provide a value for every target column in the column list, in the same order and of the correct type.

Literal values, context variables or expressions of compatible type can be substituted for any column in the source row. In this case, a source column list and a corresponding VALUES list are required.

If the column list is absent — as it is when SELECT * is used for the source expression — the column_list must contain the names of every column in the target table or view (computed columns excluded).

Of course, the column names in the source table need not be the same as those in the target table. Any type of SELECT statement is permitted, as long as its output columns exactly match the insert columns in number, order and type. Types need not be the same, but they must be assignment-compatible.

The DEFAULT VALUES clause allows insertion of a record without providing any values at all, either directly or from a SELECT statement. This is only possible if every NOT NULL or CHECKed column in the table either has a valid default declared or gets such a value from a BEFORE INSERT trigger. Furthermore, triggers providing required field values must not depend on the presence of input values.

Specifying DEFAULT VALUES is equivalent to specifying a values list with expression DEFAULT for all columns.

The OVERRIDING clause controls the behaviour of an identity column for this statement only.

An INSERT statement may optionally include a RETURNING clause to return values from the inserted rows. The clause, if present, need not contain all columns referenced in the insert statement and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE INSERT triggers.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using * or table_name.*.

The optional INTO sub-clause is only valid in PSQL.

Inserting into BLOB columns is only possible under the following circumstances:

If you assign an alias to a table or a view, the alias must be used when specifying columns and also in any column references included in other clauses.

Correct usage:

Not possible:

In the SET clause, the assignment expressions, containing the columns with the values to be set, are separated by commas. In an assignment expression, column names are on the left and the values or expressions to assign are on the right. A column may be assigned only once in the SET clause.

A column name can be used in expressions on the right. The old value of the column will always be used in these right-side values, even if the column was already assigned a new value earlier in the SET clause.

Using the expression DEFAULT will set the column to its default value (either NULL or the value specified on the DEFAULT clause of the column definition). For an identity column, specifying DEFAULT will generate a new identity value. It is possible to “update” calculated columns in the SET clause if and only if the assigned value is DEFAULT.

Data in the TSET table:

The statement:

will change the values to:

Notice that the old values (1 and 2) are used to update the b column even after the column was assigned a new value (5).

The WHERE clause sets the conditions that limit the set of records for a searched update.

In PSQL, if a named cursor is being used for updating a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned. This is a positioned update.

For string literals with which the parser needs help to interpret the character set of the data, the introducer syntax may be used. The string literal is preceded by the character set name, prefixed with an underscore character:

The ORDER BY and ROWS clauses make sense only when used together. However, they can be used separately.

If ROWS has one argument, m, the rows to be updated will be limited to the first m rows.

If two arguments are used, m and n, ROWS limits the rows being updated to rows from m to n inclusively. Both arguments are integers and start from 1.

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not updated.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to update.

An UPDATE statement may include RETURNING to return some values from the updated rows. RETURNING may include data from any column of the row, not only the columns that are updated by the statement. It can include literals or expressions not associated with columns, if there is a need for that.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

When the RETURNING set contains data from the current row, the returned values report changes made in the BEFORE UPDATE triggers, but not those made in AFTER UPDATE triggers.

The context variables OLD.fieldname and NEW.fieldname can be used as column names. If OLD. or NEW. is not specified, or if the table name (target) is specified instead, the column values returned are the NEW. ones.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a positioned update statement (WHERE CURRENT OF …​) with RETURNING always returns a single row, a normal update statement can return zero or more rows. The update is executed to completion before rows are returned. In PSQL, attempts to execute an UPDATE …​ RETURNING that affects multiple rows will result in the error “multiple rows in singleton select”. This behaviour may change in a future Firebird version.

In PSQL, the INTO clause can be used to pass the returning values to local variables. It is not available in DSQL. If no records are updated, nothing is returned and variables specified in RETURNING will keep their previous values.

Updating a BLOB column always replaces the entire contents. Even the BLOB ID, the “handle” that is stored directly in the column, is changed. BLOBs can be updated if:

See [fblangref50-dml-update-orderbyclause] for UPDATE.

The optional RETURNING clause, if present, need not contain all the columns mentioned in the statement and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE triggers, but not those in AFTER triggers. OLD.fieldname and NEW.fieldname may both be used in the list of columns to return; for field names not preceded by either of these, the new value is returned.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a statement with a RETURNING clause can return zero or more rows. The update or insert is executed to completion before rows are returned. In PSQL, if a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised. This behaviour may change in a future Firebird version.

The optional INTO sub-clause is only valid in PSQL.

Modifying data in a table, using UPDATE OR INSERT in a PSQL module. The return value is passed to a local variable, whose colon prefix is optional.

If an alias is specified for the target table or view, it must be used to qualify all field name references in the DELETE statement.

Supported usage:

Not possible:

The WHERE clause sets the conditions that limit the set of records for a searched delete.

In PSQL, if a named cursor is being used for deleting a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned. This is a positioned delete.

A PLAN clause allows the user to optimize the operation manually.

The ORDER BY clause orders the set before the actual deletion takes place. It only makes sense in combination with ROWS, but is also valid without it.

The ROWS clause limits the number of rows being deleted. Integer literals or any integer expressions can be used for the arguments m and n.

If ROWS has one argument, m, the rows to be deleted will be limited to the first m rows.

If two arguments are used, m and n, ROWS limits the rows being deleted to rows from m to n inclusively. Both arguments are integers and start from 1.

Deleting the oldest purchase:

Deleting the highest custno(s):

Deleting all sales, ORDER BY clause pointless:

Deleting one record starting from the end, i.e. from Z…​:

Deleting the five oldest groups:

No sorting (ORDER BY) is specified so 8 found records, starting from the fifth one, will be deleted:

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not deleted.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to delete.

A DELETE statement may optionally include a RETURNING clause to return values from the deleted rows. The clause, if present, need not contain all the relation’s columns and may also contain other columns or expressions.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*.

The ORDER BY can be used to influence the order in which rows are evaluated. The primary use case is when combined with RETURNING, to influence the order rows are returned.

A MERGE statement can contain a RETURNING clause to return rows added, modified or removed. The merge is executed to completion before rows are returned. The RETURNING clause can contain any columns from the target table (or updatable view), as well as other columns (eg from the source) and expressions.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

In PSQL, If a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised. This behaviour may change in a future Firebird version.

The optional INTO sub-clause is only valid in PSQL.

Column names can be qualified by the OLD or NEW prefix to define exactly what value to return: before or after modification. The returned values include the changes made by BEFORE triggers.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

For the UPDATE or INSERT action, unqualified column names, or those qualified by the target table name or alias will behave as if qualified by NEW, while for the DELETE action as if qualified by OLD.

The following example modifies the previous example to affect one line, and adds a RETURNING clause to return the old and new quantity of goods, and the difference between those values.

[fblangref50-dml-select], [fblangref50-dml-insert], [fblangref50-dml-update], [fblangref50-dml-update-or-insert], [fblangref50-dml-delete]

The EXECUTE PROCEDURE statement is most commonly used to invoke “executable” stored procedures to perform some data-modifying task at the server side — those that do not contain any SUSPEND statements in their code. They can be designed to return a result set, consisting of only one row, which is usually passed, via a set of RETURNING_VALUES() variables, to another stored procedure that calls it. Client interfaces usually have an API wrapper that can retrieve the output values into a single-row buffer when calling EXECUTE PROCEDURE in DSQL.

Invoking “selectable” stored procedures is also possible with EXECUTE PROCEDURE, but it returns only the first row of an output set which is almost surely designed to be multi-row. Selectable stored procedures are designed to be invoked by a SELECT statement, producing output that behaves like a virtual table.

Executing a block without input parameters should be possible with every Firebird client that allows the user to enter their own DSQL statements. If there are input parameters, things get trickier: these parameters must get their values after the statement is prepared, but before it is executed. This requires special provisions, which not every client application offers. (Firebird’s own isql, for one, doesn’t.)

The server only accepts question marks (“?”) as placeholders for the input values, not “:a”, “:MyParam” etc., or literal values. Client software may support the “:xxx” form though, and will preprocess it before sending it to the server.

If the block has output parameters, you must use SUSPEND or nothing will be returned.

Output is always returned in the form of a result set, just as with a SELECT statement. You can’t use RETURNING_VALUES or execute the block INTO some variables, even if there is only one result row.

Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.