Used for:  Retrieving a slice of rows from an ordered set

Available in: DSQL, PSQL

Syntax: 

Description

FIRST limits the output of a query to the first m rows. SKIP will suppress the given n rows before starting to return output.

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 rest of the set are returned.

Examples

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

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

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

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

See also:  ROWS

The columns list contains one or more comma-separated value expressions. Each expression provides a value for one output column. Alternatively, * (“select star”) can be used to stand for all the columns in a relation (i.e. a table, view or selectable stored procedure).

Syntax: 

Description

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. e.g., 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 always mandatory 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 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:

Examples of SELECT queries with different types of column lists

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:

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

See also:  Scalar Functions, Aggregate 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 just a single table or view. But 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 concentrates on single-source selects. Joins are discussed in a following section.

Syntax: 

See also: Common Table Expressions (“WITH ... AS ... SELECT”).

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

Syntax: 

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

Syntax: 

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.

Examples

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

Suppose you have a table listing some children's names and the number of marbles they possess. At a certain moment, the table contains these 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 marbles or less. 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 of will consist 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.

Syntax: 

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:

Examples

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:

and 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. In order to get that extra bit of information, the non-aggregate column CLASS must be added 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:

As you may recall from the formal syntax of the columns list, the AS keyword is optional.

Adding more non-aggregate (or rather: 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 variables class, sex and boarding type. The aggregate results—number and average age—are given for each of these rather specific 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 very 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.

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

Syntax: 

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 studying query plans rather than running queries, SET PLANONLY ON will show the plan without executing the query.

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 very well be worth your while to examine the plan and see if you can improve on it.

A UNION 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. Other than that, they may be totally unrelated.

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

Syntax: 

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.

Examples

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:

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. To specify a sorting order for the set specification, an ORDER BY clause is used.

Syntax: 

Description

The ORDER BY consists of a comma-separated list of the columns 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 there. The ordinal position number of the column in the , 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.

Examples

Sorting the result set in ascending order, ordering by the RDB$CHARACTER_SET_ID, RDB$COLLATION_ID columns of the DB$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:

Examples, continued

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.

Used for:  Retrieving a slice of rows from an ordered set

Available in: DSQL, PSQL

Syntax: 

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

The FIRST and SKIP clauses do the same job as ROWS are not SQL-compliant. Using ROWS is thus preferable in new code. Unlike FIRST and SKIP, 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.

Characteristics of using ROWS m without a TO clause:

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

Characteristics of using ROWS m with a TO clause:

Using a TO clause without a ROWS clause:

While ROWS replaces the non-standard FIRST and SKIP syntax, there is one situation where the standard 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.

Examples

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

Retrieve the first ten names from a 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:

Syntax: 

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

The OF sub-clause does not do anything at all.

Available in: DSQL, PSQL

Used for: Limited pessimistic locking

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

Syntax: 

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.

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

Examples using explicit locking

Used for: Passing SELECT output into variables

Available in: PSQL

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

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

Examples

Selecting some aggregated values and passing them into previously declared variables min_amt, avg_amt and max_amt:

A PSQL trigger that retrieves two values as a BLOB field (using the LIST() function) and assigns it INTO a third field:

Available in: DSQL, PSQL

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: 

Example: 

CTE Notes