Subqueries-SELECT query - Firebird

A subquery is a special form of expression that is actually a SELECT query on another table, embedded within the main query specification. The embedded query expression is referred to variously as a subquery, a nested query, an inline query, and sometimes (erroneously) a sub-select.

In Firebird, up to and including version 1.5, subquery expressions are used in three ways:

  • To provide a single or multi-row input set for an INSERT query. The syntax is described in the section “The INSERT Statement” in Chapter DML Queries.
  • To specify a runtime, read-only output column for a SELECT query.
  • To provide values or conditions for search predicates.

In versions later than Firebird 1.5, a fourth implementation of subqueries appears: the “virtual table.” It is discussed briefly in Chapter Views.

Specifying a Column Using a Subquery

A runtime output column can be specified by querying a single column in another table. The output column should be given a new identifier that, for completeness, can optionally be marked by the AS keyword. See the section “Derived Fields and Column Aliasing” in Chapter Expressions and Predicates.

The nested query must always have a WHERE condition to restrict output to a single column from a single row (known as a scalar query); otherwise, you will see some variant of the error message “Multiple rows in singleton select.”

This query uses a subquery to derive an output column:

The subquery targets a single postcode in order to return the start time of a transport route. The postcode columns in both the main query and the subquery could be substituted with replaceable parameters. To make the query more general and more useful, we can use a correlated subquery.

Correlated Subqueries

When the data item extracted in the nested subquery needs to be selected in the context of a linking value in the current row of the main query, a correlated subquery is possible. Firebird requires fully qualified identifiers in correlated subqueries.

In the next example, the linking columns in the main query and the subquery are correlated, and table aliasing is enforced to eliminate any ambiguity:

The query returns one row for each selected member, whether or not a transport route exists that matches the member’s postcode. START_TIME will be NULL for non-matches.

Subquery or Join?

The query in the previous example could have been framed using a left join instead of the subquery:

The relative cost of this query and the preceding one, using the subquery, is similar. Although each may arrive at the result by a different route, both require a full scan of the searched stream with evaluation from the searching stream.

Cost differences can become significant when the same correlated subquery is used in place of an inner join:

The inner join does not have to traverse every row of the searched stream, since it discards any row in the searched stream (ROUTES) that does not match the search condition. In contrast, the context of the correlated subquery changes with each row, with no condition to exclude any having a non-matching POSTCODE from being scanned. Thus, the correlated subquery must be run once for each row in the set.

If the output set is potentially large, consider how important the need is to do an inclusive search. A well-judged correlated subquery is useful for small sets. There is no absolute threshold of numbers for choosing one over another. As always, testing under real-life load conditions is the only reliable way to decide what works best for your own particular requirements.

When Not to Consider a Subquery

The subquery approach becomes outlandish when you need to fetch more than a single field from the same table. A subquery can return one and only one field. To fetch multiple fields, a separate correlated subquery and alias is required for each field fetched. If a left join is possible to meet these conditions, it should always be chosen.

Searching Using a Subquery

The use of existential predicators with subqueries—especially the EXISTS( ) predicator —was discussed in the previous chapter. Subqueries can also be used in other ways to predicate searc conditions for WHERE clauses and groupings.

Re-entrant Subqueries

A query can use a re-entrant subquery to set a search condition derived from the same table. Table aliasing is mandatory. In the following example, the statement subqueries the main table to find the date of the latest transaction, in order to set the search condition for the main query:

Inserting Using a Subquery with Joins

In Chapter DML Queries, we examined the inline select method of feeding data into an INSERT statement, for example:

The method is not limited to a single-stream query. Your input subquery can be joined. This capability can be very useful when you need to export denormalized data to an external table for use in another application, such as a spreadsheet, desktop database, or off-the-shelf accounting application, for example:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics