SQL Derived Table Support

Go Up to SELECT

Introduction

Note: Terms enclosed in angle brackets represent grammar production rules in the official SQL Standard document. Please refer to that document for further definition of those terms. After initial usage, the terms are referenced in "un-angle bracket" form.

Derived tables are temporary sets of records that can be used inside another queries. Derived tables can shorten long queries, or break complex processes into logical steps. The derived table syntax enhances the abilities of existing applications to use InterBase as a backend RDMBS. A <derived table> is a <table reference> denoted in the form of a <query expression>, which is evaluated to return the rows of the table. The query expression can be embedded directly in the SQL statement together with a correlation name used to identify the derived table.

Alternatively, query expressions named by an identifier can be listed in a clause that precedes the SQL statement and the named queries referenced by their identifiers in the SQL statement. This is informally known as "common table expressions" although the SQL standard does not officially recognize that nomenclature. A Common Table Expression (CTE) returns a temporary result set than can be referenced in SELECT, INSERT, UPDATE, or DELETE statements. CTE's can help to simplify complex subqueries and joins, making the code more readable and easier to maintain.

InterBase 2017 introduces the SQL reserved keyword RECURSIVE, however, recursive queries are not not supported in the initial release of InterBase 2017.

In the SQL standard, the grammar production rule denoted by a table reference represents an operand of the <from clause>. Before now, InterBase SQL only supported <joined table> and <table name> as table references.

Usage

You can consider a derived table as a View with statement-level scope. A derived table allows developers the expressive flexibility to use a view-like structure without defining a database schema view. It also allows users to obtain the same benefit in an ad hoc query without requiring database administration to create a view definition.

As a simple example, consider searching for entry level jobs in the IB example Employee database:

SELECT ELJ.JOB_CODE, ELJ.JOB_TITLE FROM (SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000) AS ELJ;

This is equivalent to:

CREATE VIEW ENTRY_LEVEL_JOB AS SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000;
SELECT ELJ.JOB_CODE, ELJ.JOB_TITLE FROM ENTRY_LEVEL_JOB AS ELJ;

It is also possible to use <with clause> to list a series of named queries before the SQL statement property when the derived table is referenced multiple times in the statement or for a recursive query.

WITH ELJ AS (SELECT JOB_CODE, JOB_TITLE FROM JOB WHERE MAX_SALARY < 50000)
      SELECT JOB_CODE, JOB_TITLE FROM ELJ;

The following SQL syntax for derived tables is taken from the SQL Standard. Please note that InterBase does not support <derived column list> clause for a <correlation name> so that this aspect of derived table support will be missing in the release.

<table reference> ::=
         <table factor>
       | <joined table>
<table factor> ::=
        <table primary> [ <sample clause> ]
<table primary> ::=
       <table or query name> [ <query system time period specification> ]
               [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
     | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
<derived table> ::=
       <table subquery>
<table subquery> ::=
       <subquery>
<subquery> ::=
       <left paren> <query expression> <right paren>

The following SQL syntax for the WITH clause is taken from the SQL Standard. Please note that InterBase does not support <order by clause>, <result offset clause> , <fetch first clause> for a query expression; it does so with equivalent syntax only at the statement level.

<query expression> ::=
       [ <with clause> ] <query expression body>
             [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
<with clause> ::=
       WITH [ RECURSIVE ] <with list>
<with list> ::=
       <with list element> [ { <comma> <with list element> }... ]
<with list element> ::=
       <query name> [ <left paren> <with column list> <right paren> ]
              AS <table subquery>
<with column list> ::=
       <column name list>
<query expression body> ::=
       <query term>
     | <query expression body> UNION [ ALL | DISTINCT ]
           [ <corresponding spec> ] <query term>
<query term> ::=
       <query primary>
<query primary> ::=
        <simple table>
      | <left paren> <query expression body>
            [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
        <right paren>
<simple table> ::=
       <query specification>

Requirements and Constraints

1. Users must have proper access privileges on the underlying base tables and views accessed by a derived table.
2. The derived table syntax can be used in triggers and stored procedures as well as user applications.
3. Dynamic SQL supports derived table syntax
4. Interactive SQL (isql) supports derived table syntax.
5. Embedded SQL (static) doesn't support derived table syntax.

Migration issues SQL statements that use derived table syntax only execute on InterBase servers or mobile editions running 2017 and subsequent editions. Any existing client can submit a SQL statement with derived table syntax using the InterBase DSQL syntax. Third-party supported DB frameworks, including InterClient, should pass through such statements without issues.