6.1.3. The FROM clause

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

SELECT
  ...
  FROM <source>
  [<joins>]
  [...]
<source> ::=
  {   table
    | view
    | selectable-stored-procedure [(<args>)]
    | <derived-table>
    | <common-table-expression>
  } [[AS] alias]
<derived-table> ::=
  (<select-statement>) [[AS] alias] [(<column-aliases>)]
<common-table-expression> ::=
  WITH [RECURSIVE] <cte-def> [, <cte-def> ...]
  <select-statement>
<cte-def> ::= name [(<column-aliases>)] AS (<select-statement>)
<column-aliases> ::= column-alias [, column-alias ...]

Selecting FROM a table or view

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

Examples

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

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

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

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

SELECT FRUIT.PEARS
FROM FRUIT F;

Selecting FROM a stored procedure

A selectable stored procedure is a procedure that:

• contains at least one output parameter, and

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

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

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

select * from suspicious_transactions
  where assignee = 'John'

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

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

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

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

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

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

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

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

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

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

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

See also

Stored Procedures, CREATE PROCEDURE

Selecting FROM a derived table

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

Syntax

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

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

Sample using a derived table

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

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

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

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

A more useful example

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

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

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

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

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

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

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

Selecting FROM a CTE

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

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

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

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

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

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

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

See also

Common Table Expressions (WITH …​ AS …​ SELECT).