Used forRetrieving a slice of rows from an ordered set

Available inDSQL, PSQL

Syntax

  |SELECT
  |  [FIRST <m>] [SKIP <n>]
  |  FROM ...
  |  ...
  | 
  |<m>, <n>  ::=
  |    <integer-literal>
  |  | <query-parameter>
  |  | (<integer-expression>)

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.

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

Syntax

   |SELECT
   |  [...]
   |  [{DISTINCT | ALL}] <output-column> [, <output-column> ...]
   |  [...]
   |  FROM ...
   | 
   |<output-column> ::=
   |  { [<qualifier>.]*
   |  | <value-expression> [COLLATE collation] [[AS] alias] }
   | 
   |<value-expression> ::=
   |  { [<qualifier>.]table-column
   |  | [<qualifier>.]view-column
   |  | [<qualifier>.]selectable-SP-outparm
   |  | <literal>
   |  | <context-variable>
   |  | <function-call>
   |  | <single-value-subselect>
   |  | <CASE-construct>
   |  | any other expression returning a single
   |    value of a Firebird data type or NULL }
   | 
   |<qualifier> ::= a relation name or alias

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

• DISTINCT filters out any duplicate rows. That is, if two or more rows have the same values in every corresponding column, only one of them is included in the result set

• ALL is the default: it returns all of the rows, including duplicates. ALL is rarely used; it is supported for compliance with the SQL standard.

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:

• The ordering, if an ORDER BY clause is also present and it involves that column

• Grouping, if the column is part of a GROUP BY clause

• The rows retrieved (and hence the total number of rows in the result set), if DISTINCT is used

Examples of SELECT queries with different types of column lists

A simple SELECT using only column names:

  |select cust_id, cust_name, phone
  |  from customers
  |  where city = 'London'

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

  |select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
  |  from contacts
  |  where date_last_purchase(id) = current_date

A query with two subselects:

  |select p.fullname,
  |  (select name from classes c where c.id = p.class) as class,
  |  (select name from mentors m where m.id = p.mentor) as mentor
  |from pupils p

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

  |select p.fullname,
  |  c.name as class,
  |  m.name as mentor
  |  join classes c on c.id = p.class
  |from pupils p
  |  join mentors m on m.id = p.mentor

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

  |select case upper(sex)
  |    when 'F' then 'Mrs.'
  |    when 'M' then 'Mr.'
  |    else ''
  |  end as title,
  |  lastname,
  |  address
  |from employees

Query using a window function. Ranks employees by salary.

  |SELECT
  |  id,
  |  salary,
  |  name ,
  |  DENSE_RANK() OVER (ORDER BY salary) AS EMP_RANK
  |FROM employees
  |ORDER BY salary;

Querying a selectable stored procedure:

  |select * from interesting_transactions(2010, 3, 'S')
  |  order by amount

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:

  |select fieldcount,
  |  count(relation) as num_tables
  |from (select r.rdb$relation_name as 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

Asking the time through a context variable (CURRENT_TIME):

  |select current_time from rdb$database

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:

  |select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
  |  from rdb$database

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

  |select rdb$character_set_name from rdb$database

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

See alsoFunctions, 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 just 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 <source>
   |  [<joins>]
   |  [...]
   | 
   |<source> ::=
   |    table [[AS] alias]
   |  | selectable-stored-procedure [(<args>)] [[AS] alias]
   |  | <derived-table>
   | 
   |<derived-table> ::=
   |  (<select-statement>) [[AS] alias] [(<column-aliases>)]
   | 
   |<column-aliases> ::= column-alias [, column-alias ...]

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

  |select * from suspicious_transactions
  |  where assignee = 'John'

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

  |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');

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

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

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

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

   |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

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

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

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

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

  |SELECT
  |  UUID_TO_CHAR(X) AS C1,
  |  UUID_TO_CHAR(X) AS C2,
  |  UUID_TO_CHAR(X) AS C3
  |FROM (SELECT GEN_UUID() AS X
  |      FROM RDB$DATABASE) T;

  |C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
  |C3  EB176C10-F754-4689-8B84-64B666381154

  |SELECT
  |  UUID_TO_CHAR(X) AS C1,
  |  UUID_TO_CHAR(X) AS C2,
  |  UUID_TO_CHAR(X) AS C3
  |FROM (SELECT GEN_UUID() AS X
  |      FROM RDB$DATABASE
  |      UNION ALL
  |      SELECT NULL FROM RDB$DATABASE WHERE 1 = 0) T;

  |C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

  |SELECT
  |  UUID_TO_CHAR(X) AS C1,
  |  UUID_TO_CHAR(X) AS C2,
  |  UUID_TO_CHAR(X) AS C3
  |FROM (SELECT
  |        (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
  |      FROM RDB$DATABASE) T;

  |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

   |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

  |WITH T (X) AS (
  |  SELECT GEN_UUID()
  |  FROM RDB$DATABASE)
  |SELECT
  |  UUID_TO_CHAR(X) as c1,
  |  UUID_TO_CHAR(X) as c2,
  |  UUID_TO_CHAR(X) as c3
  |FROM T

  |C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
  |C3  EB176C10-F754-4689-8B84-64B666381154

   |WITH T (X) AS (
   |  SELECT GEN_UUID()
   |  FROM RDB$DATABASE
   |  UNION ALL
   |  SELECT NULL FROM RDB$DATABASE WHERE 1 = 0)
   |SELECT
   |  UUID_TO_CHAR(X) as c1,
   |  UUID_TO_CHAR(X) as c2,
   |  UUID_TO_CHAR(X) as c3
   |FROM T;

  |C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
  |C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

  |WITH T (X) AS (
  |  SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
  |  FROM RDB$DATABASE)
  |SELECT
  |  UUID_TO_CHAR(X) as c1,
  |  UUID_TO_CHAR(X) as c2,
  |  UUID_TO_CHAR(X) as c3
  |FROM T;

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

   |SELECT
   |   ...
   |   FROM <source>
   |   [<joins>]
   |   [...]
   | 
   |<source> ::=
   |    table [[AS] alias]
   |  | selectable-stored-procedure [(<args>)] [[AS] alias]
   |  | <derived-table>
   | 
   |<joins> ::= <join> [<join> ...]
   | 
   |<join> ::=
   |    [<join-type>] JOIN <source> <join-condition>
   |  | NATURAL [<join-type>] JOIN <source>
   |  | {CROSS JOIN | ,} <source>
   | 
   |<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
   | 
   |<join-condition> ::= ON <condition> | USING (<column-list>)

  |select *
  |  from A
  |  join B on A.id = B.code;

  |select *
  |  from A
  |  inner join B on A.id = B.code;

  |select *
  |  from A
  |  left [outer] join B on A.id = B.code;

  |select *
  |  from A
  |  right [outer] join B on A.id = B.code

  |select *
  |  from A
  |  full [outer] join B on A.id = B.code

  |<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>
  | 
  |<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]
  | 
  |<join-condition> ::= ON <condition> | USING (<column-list>)

  |/* Select all Detroit customers who made a purchase
  |   in 2013, along with the purchase details: */
  |select * from customers c
  |  join sales s on s.cust_id = c.id
  |  where c.city = 'Detroit' and s.year = 2013;

  |/* Same as above, but include non-buying customers: */
  |select * from customers c
  |  left join sales s on s.cust_id = c.id
  |  where c.city = 'Detroit' and s.year = 2013;

  |/* For each man, select the women who are taller than he.
  |   Men for whom no such woman exists are not included. */
  |select m.fullname as man, f.fullname as woman
  |  from males m
  |  join females f on f.height > m.height;

  |/* Select all pupils with their class and mentor.
  |   Pupils without a mentor are also included.
  |   Pupils without a class are not included. */
  |select p.firstname, p.middlename, p.lastname,
  |       c.name, m.name
  |  from pupils p
  |  join classes c on c.id = p.class
  |  left join mentors m on m.id = p.mentor;

  |select * from flotsam f
  |  join jetsam j
  |  on f.sea = j.sea
  |  and f.ship = j.ship;

  |select * from flotsam
  |  join jetsam using (sea, ship)

  |select f.*, j.*
  |  from flotsam f
  |  join jetsam j using (sea, ship);

  |SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE x = 0;
  | 
  |-- PLAN JOIN (A NATURAL , B INDEX (RDB$2))

  |SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE a.x = 0;
  |-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))
  | 
  |SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE b.x = 0;
  |-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

  |<natural-join> ::= NATURAL [<join-type>] JOIN <source>
  | 
  |<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

  |create table TA (
  |  a bigint,
  |  s varchar(12),
  |  ins_date date
  |);

  |create table TB (
  |  a bigint,
  |  descr varchar(12),
  |  x float,
  |  ins_date date
  |);

  |select * from TA
  |  natural join TB;

  |select * from TA
  |  join TB using (a, ins_date);

  |<cross-join> ::= {CROSS JOIN | ,} <source>

  |select * from TA
  |  cross join TB;

  |select * from TA
  |  join TB on 1 = 1;

  |select m.name, s.size, c.name
  |  from materials m
  |  cross join sizes s
  |  cross join colors c;

  |/*
  | * A sample of all Detroit customers who
  | * made a purchase.
  | */
  |SELECT *
  |FROM customers c, sales s
  |WHERE s.cust_id = c.id AND c.city = 'Detroit'

  |SELECT *
  |FROM TA, TB
  |JOIN TC ON TA.COL1 = TC.COL1
  |WHERE TA.COL2 = TB.COL2

  |SELECT *
  |FROM TA, TB
  |JOIN TC ON TB.COL1 = TC.COL1
  |WHERE TA.COL2 = TB.COL2

  |select *
  |  from A join B
  |  on A.id is not distinct from B.code;

  |select *
  |  from A join B
  |  on A.id is distinct from B.code;

  |select a, b, c
  |  from TA
  |  join TB on TA.a = TB.a;

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

  |SELECT ...
  |  FROM ...
  |  [...]
  |  WHERE <search-condition>
  |  [...]

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

  |select genus, species from mammals
  |  where family = 'Felidae'
  |  order by genus;

  |select * from persons
  |  where birthyear in (1880, 1881)
  |     or birthyear between 1891 and 1898;

  |select name, street, borough, phone
  |  from schools s
  |  where exists (select * from pupils p where p.school = s.id)
  |  order by borough, street;

  |select * from employees
  |  where salary >= 10000 and position <> 'Manager';

  |select name from wrestlers
  |  where region = 'Europe'
  |    and weight > all (select weight from shot_putters
  |                      where region = 'Africa');

  |select id, name from players
  |  where team_id = (select id from teams where name = 'Buffaloes');

  |select sum (population) from towns
  |  where name like '%dam'
  |  and province containing 'land';

  |select password from usertable
  |  where username = current_user;

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:

  |select list(child) from marbletable where marbles > 10;

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

If you negate the expression:

  |select list(child) from marbletable where not marbles > 10

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:

  |select list(child) from marbletable where marbles <= 10;

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:

  |select list(child) from marbletable
  |where marbles <= 10 or marbles is null;

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.

  |select name, address, phone frome stores
  |  where city = ? and class = ?;

  |select * from pants
  |  where model = :model and size = :size and color = :col;

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

   |SELECT ... FROM ...
   |  GROUP BY <grouping-item> [, <grouping-item> ...]
   |  [HAVING <grouped-row-condition>]
   |  ...
   | 
   |<grouping-item> ::=
   |    <non-aggr-select-item>
   |  | <non-aggr-expression>
   | 
   |<non-aggr-select-item> ::=
   |    column-copy
   |  | column-alias
   |  | column-position

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:

1. By copying the item verbatim from the select list, e.g. class or 'D:' || upper(doccode).

2. By specifying the column alias, if it exists.

3. By specifying the column position as an integer literal between 1 and the number of columns. Integer values resulting from expressions or parameter substitutions are simply invariables and will be used as such in the grouping. They will have no effect though, as their value is the same for each row.

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

• Columns from the source table that are not in the select list, or non-aggregate expressions based on such columns. Adding such columns may further subdivide the groups. However, since these columns are not in the select list, you can’t tell which aggregated row corresponds to which value in the column. So, in general, if you are interested in this information, you also include the column or expression in the select list — which brings you back to the rule: every non-aggregate column in the select list must also be in the grouping list.

• Expressions that aren’t dependent on the data in the underlying set, e.g. constants, context variables, single-value non-correlated subselects etc. This is only mentioned for completeness, as adding such items is utterly pointless: they don’t affect the grouping at all. Harmless but useless items like these may also figure in the select list without being copied to the grouping list.

Examples

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

  |select count(*), avg(age) from students
  |  where sex = 'M';

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:

  |select count(*), avg(age), current_date from students
  |  where sex = 'M';

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:

  |select count(*), avg(age) from students
  |  where sex = 'M'
  |  group by class;

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

  |select class, count(*), avg(age) from students
  |  where sex = 'M'
  |  group by class;

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:

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class;

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:

  |select class,
  |       sex,
  |       boarding_type,
  |       count(*) as number,
  |       avg(age) as avg_age
  |  from students
  |  group by class, sex, boarding_type;

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

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having count(*) >= 5;

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having max(age) - min(age) > 1.2;

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M'
  |  group by class
  |  having class starting with '3';

  |select class,
  |       count(*) as num_boys,
  |       avg(age) as boys_avg_age
  |  from students
  |  where sex = 'M' and class starting with '3'
  |  group by class;

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

Syntax

   |PLAN <plan-expr>
   | 
   |<plan-expr> ::=
   |    (<plan-item> [, <plan-item> ...])
   |  | <sorted-item>
   |  | <joined-item>
   |  | <merged-item>
   |  | <hash-item>
   | 
   |<sorted-item> ::= SORT (<plan-item>)
   | 
   |<joined-item> ::=
   |  JOIN (<plan-item>, <plan-item> [, <plan-item> ...])
   | 
   |<merged-item> ::=
   |  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])
   | 
   |<hash-item> ::=
   |  HASH (<plan-item>, <plan-item> [, <plan-item> ...])
   | 
   |<plan-item> ::= <basic-item> | <plan-expr>
   | 
   |<basic-item> ::=
   |  <relation> { NATURAL
   |             | INDEX (<indexlist>)
   |             | ORDER index [INDEX (<indexlist>)] }
   | 
   |<relation> ::= table | view [table]
   | 
   |<indexlist> ::= index [, index ...]

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

  |select * from students
  |  plan (students natural);

  |Select Expression
  |  -> Table "STUDENTS" Full Scan

  |select * from students
  |  where class = '3C'
  |  plan (students index (ix_stud_class));

  |Select Expression
  |  -> Filter
  |    -> Table "STUDENTS" Access By ID
  |      -> Bitmap
  |        -> Index "IX_STUD_CLASS" Range Scan (full match)

  |select * from students
  |  plan (students order pk_students)
  |  order by id;

  |Select Expression
  |  -> Table "STUDENTS" Access By ID
  |    -> Index "PK_STUDENTS" Full Scan

  |select * from students
  |  where class >= '3'
  |  plan (students order pk_students index (ix_stud_class))
  |  order by id;

  |Select Expression
  |  -> Filter
  |    -> Table "STUDENTS" Access By ID
  |      -> Index "PK_STUDENTS" Full Scan
  |        -> Bitmap
  |          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

  |select * from students
  |  where class >= '3'
  |  plan (students order ix_stud_class index (ix_stud_class))
  |  order by class;

  |Select Expression
  |  -> Filter
  |    -> Table "STUDENTS" Access By ID
  |      -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
  |        -> Bitmap
  |          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

  |select * from students
  |  plan sort (students natural)
  |  order by name;

  |Select Expression
  |  -> Sort (record length: 128, key length: 56)
  |    -> Table "STUDENTS" Full Scan

  |select * from students
  |  where class >= '3'
  |  plan sort (students index (ix_stud_class))
  |  order by name;

  |elect Expression
  |  -> Sort (record length: 136, key length: 56)
  |    -> Filter
  |      -> Table "STUDENTS" Access By ID
  |        -> Bitmap
  |          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

  |select * from freshmen
  |  plan (freshmen students natural);

  |Select Expression
  |  -> Table "STUDENTS" as "FRESHMEN" Full Scan

  |select * from freshmen
  |  where id > 10
  |  plan sort (freshmen students index (pk_students))
  |  order by name desc;

  |Select Expression
  |  -> Sort (record length: 144, key length: 24)
  |    -> Filter
  |      -> Table "STUDENTS" as "FRESHMEN" Access By ID
  |        -> Bitmap
  |          -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan join (s natural, c index (pk_classes));

  |Select Expression
  |  -> Nested Loop Join (inner)
  |    -> Table "STUDENTS" as "S" Full Scan
  |    -> Filter
  |      -> Table "CLASSES" as "C" Access By ID
  |        -> Bitmap
  |          -> Index "PK_CLASSES" Unique Scan

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan join (s order pk_students, c index (pk_classes))
  |  order by s.id;

  |Select Expression
  |  -> Nested Loop Join (inner)
  |    -> Table "STUDENTS" as "S" Access By ID
  |      -> Index "PK_STUDENTS" Full Scan
  |    -> Filter
  |      -> Table "CLASSES" as "C" Access By ID
  |        -> Bitmap
  |          -> Index "PK_CLASSES" Unique Scan

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  plan sort (join (s natural, c index (pk_classes)))
  |  order by s.name;

  |Select Expression
  |  -> Sort (record length: 152, key length: 12)
  |    -> Nested Loop Join (inner)
  |      -> Table "STUDENTS" as "S" Full Scan
  |      -> Filter
  |        -> Table "CLASSES" as "C" Access By ID
  |          -> Bitmap
  |            -> Index "PK_CLASSES" Unique Scan

  |select s.id, s.name, s.class, c.mentor
  |  from students s
  |  join classes c on c.name = s.class
  |  where s.class <= '2'
  |  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  |  order by s.name;

   |Select Expression
   |  -> Sort (record length: 152, key length: 12)
   |    -> Nested Loop Join (inner)
   |      -> Filter
   |        -> Table "STUDENTS" as "S" Access By ID
   |          -> Bitmap
   |            -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
   |      -> Filter
   |        -> Table "CLASSES" as "C" Access By ID
   |          -> Bitmap
   |            -> Index "PK_CLASSES" Unique Scan

  |select s.id, s.name, s.class, c.mentor
  |  from classes c
  |  left join students s on c.name = s.class
  |  where s.class <= '2'
  |  plan sort (join (c natural, s index (fk_student_class)))
  |  order by s.name;

  |Select Expression
  |  -> Sort (record length: 192, key length: 56)
  |    -> Filter
  |      -> Nested Loop Join (outer)
  |        -> Table "CLASSES" as "C" Full Scan
  |        -> Filter
  |          -> Table "STUDENTS" as "S" Access By ID
  |            -> Bitmap
  |              -> Index "FK_STUDENT_CLASS" Range Scan (full match)

  |select *
  |  from students s
  |  join classes c on c.cookie = s.cookie
  |  plan hash (c natural, s natural)

  |Select Expression
  |  -> Filter
  |    -> Hash Join (inner)
  |      -> Table "STUDENTS" as "S" Full Scan
  |      -> Record Buffer (record length: 145)
  |        -> Table "CLASSES" as "C" Full Scan

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  plan merge (sort (c natural), sort (s natural));

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  plan sort (merge (sort (c natural), sort (s natural)))
  |  order by c.name, s.id;

  |select * from students s
  |  join classes c on c.cookie = s.cookie
  |  where s.id < 10 and s.class <= '2'
  |  plan sort (merge (sort (c natural),
  |                    sort (s index (pk_students, fk_student_class))))
  |  order by c.name, s.id;

  |select *
  |from color
  |where exists (
  |  select *
  |  from hors
  |  where horse.code_color = color.code_color
  |  plan (horse index (fk_horse_color)))
  |plan (color natural)

  |MERGE (unsorted stream, unsorted stream)

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

   |<union> ::=
   |  <individual-select>
   |  UNION [{DISTINCT | ALL}]
   |  <individual-select>
   |  [
   |    [UNION [{DISTINCT | ALL}]
   |    <individual-select>
   |    ...
   |  ]
   |  [<union-wide-clauses>]
   | 
   |<individual-select> ::=
   |  SELECT
   |  [TRANSACTION name]
   |  [FIRST m] [SKIP n]
   |  [{DISTINCT | ALL}] <columns>
   |  [INTO <host-varlist>]
   |  FROM <source> [[AS] alias]
   |  [<joins>]
   |  [WHERE <condition>]
   |  [GROUP BY <grouping-list>
   |  [HAVING <aggregate-condition>]]
   |  [PLAN <plan-expr>]
   | 
   |<union-wide-clauses> ::=
   |  [ORDER BY <ordering-list>]
   |  [{ ROWS <m> [TO <n>]
   |   | [OFFSET n {ROW | ROWS}]
   |     [FETCH {FIRST | NEXT} [m] {ROW | ROWS} ONLY]
   |  }]
   |  [FOR UPDATE [OF <columns>]]
   |  [WITH LOCK]
   |  [INTO <PSQL-varlist>]

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:

  |select id, title, artist, length, 'CD' as medium
  |  from cds
  |union
  |select id, title, artist, length, 'LP'
  |  from records
  |union
  |select id, title, artist, length, 'MC'
  |  from cassettes
  |order by 3, 2  -- artist, title;

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

  |select c.*, 'CD' as medium
  |  from cds c
  |union
  |select r.*, 'LP'
  |  from records r
  |union
  |select c.*, 'MC'
  |  from cassettes c
  |order by 3, 2  -- artist, title;

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.

  |select name, phone from translators
  |  union distinct
  |select name, telephone from proofreaders;

A UNION within a subquery:

  |select name, phone, hourly_rate from clowns
  |where hourly_rate < all
  |  (select hourly_rate from jugglers
  |     union
  |   select hourly_rate from acrobats)
  |order by hourly_rate;

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.

Syntax

  |SELECT ... FROM ...
  |...
  |ORDER BY <ordering-item> [, <ordering-item> …]
  | 
  |<ordering-item> ::=
  |  {col-name | col-alias | col-position | <expression>}
  |  [COLLATE collation-name]
  |  [ASC[ENDING] | DESC[ENDING]]
  |  [NULLS {FIRST|LAST}]

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

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY RDB$CHARACTER_SET_ID, RDB$COLLATION_ID;

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY CHARSET_ID, COLL_ID;

  |SELECT
  |  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  |  RDB$COLLATION_ID AS COLL_ID,
  |  RDB$COLLATION_NAME AS NAME
  |FROM RDB$COLLATIONS
  |ORDER BY 1, 2;

  |SELECT *
  |FROM RDB$COLLATIONS
  |ORDER BY 3, 2;

  |SELECT
  |    BOOKS.*,
  |    FILMS.DIRECTOR
  |FROM BOOKS, FILMS
  |ORDER BY 2;

  |SELECT *
  |FROM MSG
  |ORDER BY PROCESS_TIME DESC NULLS FIRST;

  |SELECT
  |  DOC_NUMBER, DOC_DATE
  |FROM PAYORDER
  |UNION ALL
  |SELECT
  |  DOC_NUMBER, DOC_DATE
  |FROM BUDGORDER
  |ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST;

Used forRetrieving a slice of rows from an ordered set

Available inDSQL, PSQL

Syntax

  |SELECT <columns> FROM ...
  |  [WHERE ...]
  |  [ORDER BY ...]
  |  ROWS m [TO n]

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.

Characteristics of using ROWS m without a TO clause:

• If m is greater than the total number of records in the intermediate data set, the entire set is returned

• If m = 0, an empty set is returned

• If m < 0, the SELECT statement call fails with an error

Calling ROWS m TO n retrieves 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:

• If m is greater than the total number of rows in the intermediate set and n >= m, an empty set is returned

• If m is not greater than n and n is greater than the total number of rows in the intermediate set, the result set will be limited to rows starting from m, up to the end of the set

• If m < 1 and n < 1, the SELECT statement call fails with an error

• If n = m - 1, an empty set is returned

• If n < m - 1, the SELECT statement call fails with an error

Using a TO clause without a ROWS clause:

While ROWS replaces 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.

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 1 TO 10;

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 10;

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 11 TO (SELECT COUNT(*) FROM People);

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS (SELECT COUNT(*) - 9 FROM People)
  |TO (SELECT COUNT(*) FROM People);

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |ROWS 81 TO 100;

Used forRetrieving a slice of rows from an ordered set

Available inDSQL, PSQL

Syntax

  |SELECT <columns> FROM ...
  |  [WHERE ...]
  |  [ORDER BY ...]
  |  [OFFSET <m> {ROW | ROWS}]
  |  [FETCH {FIRST | NEXT} [ <n> ] { ROW | ROWS } ONLY]
  | 
  |<m>, <n>  ::=
  |    <integer-literal>
  |  | <query-parameter>

The OFFSET and FETCH clauses are an SQL:2008 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 <n> is left out of the FETCH clause (eg 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 (eg making the query more readable or grammatically correct). Technically 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.

  |SELECT *
  |FROM T1
  |ORDER BY COL1
  |OFFSET 10 ROWS

  |SELECT *
  |FROM T1
  |ORDER BY COL1
  |FETCH FIRST 10 ROWS ONLY

   |SELECT *
   |FROM (
   |  SELECT *
   |  FROM T1
   |  ORDER BY COL1 DESC
   |  OFFSET 1 ROW
   |  FETCH NEXT 10 ROWS ONLY
   |) a
   |ORDER BY a.COL1
   |FETCH FIRST ROW ONLY

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |FETCH NEXT 10 ROWS ONLY;

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |OFFSET 10 ROWS;

  |SELECT id, name
  |FROM (
  |  SELECT id, name
  |  FROM People
  |  ORDER BY name DESC
  |  FETCH FIRST 10 ROWS ONLY
  |) a
  |ORDER BY name ASC;

  |SELECT id, name
  |FROM People
  |ORDER BY name ASC
  |OFFSET 80 ROWS
  |FETCH NEXT 20 ROWS;

Syntax

  |SELECT ... FROM single_table
  |  [WHERE ...]
  |  [FOR UPDATE [OF <column_list>]]

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

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

Used forLimited pessimistic locking

Available inDSQL, PSQL

Syntax

  |SELECT ... FROM single_table
  |  [WHERE ...]
  |  [FOR UPDATE [OF <column_list>]]
  |  WITH LOCK

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

1. extremely small (ideally singleton), and

2. precisely controlled by the application code.

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:

• in a subquery specification

• for joined sets

• with the DISTINCT operator, a GROUP BY clause or any other aggregating operation

• with a view

• with the output of a selectable stored procedure

• with an external table

• with a UNION query

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:

Used forPassing SELECT output into variables

Available inPSQL

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

  |SELECT [...] <column-list>
  |FROM ...
  |[...]
  |[INTO <variable-list>]
  | 
  |<variable-list> ::= [:]psqlvar [, [:]psqlvar ...]

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.

Examples

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

     |select min(amount), avg(cast(amount as float)), max(amount)
     |  from orders
     |  where artno = 372218
     |  into min_amt, avg_amt, max_amt;
   

   ⓘ

   Note

   The CAST serves to make the average a real number; otherwise, since amount is presumably an integer field, SQL rules would truncate it to the nearest lower integer.

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

     |select list(name, ', ')
     |  from persons p
     |  where p.id in (new.father, new.mother)
     |  into new.parentnames;
   

Available inDSQL, PSQL

Syntax

  |<cte-construct> ::=
  |  <cte-defs>
  |  <main-query>
  | 
  |<cte-defs> ::= WITH [RECURSIVE] <cte> [, <cte> ...]
  | 
  |<cte> ::= name [(<column-list>)] AS (<cte-stmt>)
  | 
  |<column-list> ::= column-alias [, column-alias ...]

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.

CTE Notes

• A CTE definition can contain any legal SELECT statement, as long as it doesn’t have a WITH…​ preamble of its own (no nesting).

• CTEs defined for the same main query can reference each other, but care should be taken to avoid loops.

• CTEs can be referenced from anywhere in the main query.

• Each CTE can be referenced multiple times in the main query, using different aliases if necessary.

• When enclosed in parentheses, CTE constructs can be used as subqueries in SELECT statements, but also in UPDATEs, MERGEs etc.

• In PSQL, CTEs are also supported in FOR loop headers:

    |for
    |  with my_rivers as (select * from rivers where owner = 'me')
    |    select name, length from my_rivers into :rname, :rlen
    |do
    |begin
    |  ..
    |end