FirebirdSQL logo

The FROM clause

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.

Syntax
SELECT
  ...
  FROM <table-reference> [, <table-reference> ...]
  [...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]
Table 1. Arguments for the FROM Clause
Argument Description

table-name

Name of a table or view

query-name

Name of a CTE

package-name

Name of a package

procedure-name

Name of a selectable stored procedure

procedure-args

Selectable stored procedure arguments

derived-table

Derived table query expression

correlation-name

The alias of a data source (table, view, procedure, CTE, derived table)

column-name

Name or alias for a column in a relation, CTE or derived table

Selecting FROM a table or view

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

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
Important
Never mix column names with column aliases!

If you specify an alias for a table or a view, you must always use this alias in place of the table name whenever you query the columns of the relation (and wherever else you make a reference to columns, such as ORDER BY, GROUP BY and WHERE clauses).

Correct use:

SELECT PEARS
FROM FRUIT;

SELECT FRUIT.PEARS
FROM FRUIT;

SELECT PEARS
FROM FRUIT F;

SELECT F.PEARS
FROM FRUIT F;

Incorrect use:

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, like 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 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')