2.6 Table reference

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Common Rules
(5) Rules on joined tables
(6) Rules on derived tables in a FROM clause
(7) Notes

(1) Function

A table reference specifies a table to be retrieved or a join of tables to be retrieved. A table reference is specified in a table expression.

(2) Format

table-reference::= {table-primary | joined-table}
table-primary::=
  {[authorization-identifier.] table-identifier
   [IN (RDAREA-name-specification)]
   [[AS] correlation-name]
    [used-index-SQL-optimization-specification]
   | query-name [[AS] correlation-name ]
    [used-index-SQL-optimization-specification] | (joined-table)
   | derived-table [[AS] correlation-name [(derived-column-list)]]}
derived-table::= table-subquery
derived-column-list::= column-name-list
column-name-list::= column-name [,column-name]...
joined-table::= table-reference [{INNER | LEFT [OUTER]}]
JOIN [ join-method-SQL-optimization-specification]
       table-reference ON search-condition

(3) Operands

 {[authorization-identifier.] table-identifier
   [IN (RDAREA-name-specification)]
    [[AS] correlation-name]
    [used-index-SQL-optimization-specification]
  | query-name [[AS] correlation-name]
    [used-index-SQL-optimization-specification]
  | (joined-table)
  | derived-table [[AS] correlation-name [(derived-column-list)]]}

authorization-identifier
Specifies the authorization identifier for the owner of the table. When retrieving a data dictionary table, specify MASTER in authorization-identifier.
table-identifier
Specifies the name of the table to be retrieved.
[IN (RDAREA-name-specification)]
IN
Specifies the RDAREA to be accessed. This cannot be specified in the following cases:
  • If the table specified in the table identifier is a read-only view table
  • While in a derived query expression of a view definition
RDAREA-name-specification::=value-specification
Use a value specification of the VARCHAR type, CHAR type, MVARCHAR type, or MCHAR type to specify the RDAREA name to be used as the RDAREA for storing the table specified in the table identifier. To specify multiple RDAREA names, separate them with commas (,). The same name cannot be used to specify another RDAREA name; doing so results in an error. For details about the characters allowed in an RDAREA name specified using a value specification, see 1.1.7 Specification of names. Spaces before or after an RDAREA name specified using a value specification are ignored. However, if the RDAREA name is enclosed in double quotation marks ("), only the spaces outside the double quotation marks are ignored.
When specifying an RDAREA while using the inner replica facility, specify the original RDAREA name. To target a replica RDAREA, use the change current database command (pddbchg command) or PDDBACCS operand of the client environment definition to change the RDAREA to be accessed to the replica RDAREA.
query-name
Specifies the name of the table derived from the derived query expression in the WITH clause.
[AS]correlation-name
When joining a table to itself or referencing columns in the same table from an inner subquery, specifies a name to be assigned to the table to distinguish it from each other. The word AS is optional.
If a given table or query name is specified multiple times in a FROM clause, specify a correlation name so that the table or the query name can be uniquely identified. If the query name is identical to the table name, specify a correlation name so that they can be uniquely identified.
The correlation name specified in correlation-name must be distinct from any other correlation names specified in a FROM clause.
In a single FROM clause, you cannot specify a name for correlation-name that is the same as a table name that specifies that correlation name, or a name, other than a query name, that is the same as a table identifier.
If you specify a correlation name that is the same as a correlation name specified by a table name or a query name in the same FROM clause, the table name or query name in the FROM clause that specifies the correlation name does not have a valid scope.
The scope of a correlation name is the query specification that contains in a FROM clause a table reference specifying the correlation name not through a derived table, a single-row SELECT statement, and any subqueries that are internal to them.
SQL optimization specification for the index being used
For SQL optimization specification for the index being used, see 2.24 SQL optimization specification.
(joined-table)
When specifying the order of evaluation of joined tables, specify the joined tables enclosed in parentheses. If joined tables are not enclosed in parentheses, they are evaluated in sequence beginning with the leftmost table reference.

In joined-table, specify the table that is derived by an inner or outer join.

In inner join, rows are fetched one by one from the outer and inner tables, and, of those rows, rows that satisfy the search condition are retrieved. In outer join, all rows in the outer table and rows in the inner table that satisfy the search condition are retrieved. If successive outer joins are specified, the outermost two tables are evaluated first, and the resulting table is treated as an outer table, the table to the right of it is treated as an inner table, and this evaluation is repeated until all right-side tables are exhausted.

table-reference [{INNER | LEFT [OUTER]}] JOIN table-reference
Specifies this operation when processing two tables, outer and inner tables, by matching them (creating an inner or outer join).
For table-reference-1 [INNER JOIN] table-reference-2, the table (table-reference-1) of the result of the table reference specified to the left of the [INNER JOIN] becomes the outer table, and the table (table-reference-2) of the result of the table reference specified to the right becomes the inner table. Of the results of the matching, the rows that satisfy the search condition are derived.
For table-reference-1 LEFT [OUTER] JOIN table-reference-2, the table (table-reference-1) of the result of the table reference specified to the left of the LEFT [OUTER] JOIN becomes the outer table, and the table (table-reference-2) of the result of the table reference specified to the right becomes the inner table. All rows in the outer table are derived irrespective of whether the result of the matching is TRUE or FALSE. With regard to the rows in the inner table, only those rows that satisfy the search condition are derived.
SQL optimization specification for join methods
For SQL optimization specification for join methods, see 2.24 SQL optimization specification.
ON search-condition
Specifies a join condition for an inner or outer join.
In search-condition, a column of an outer or inner table can be specified.
Column specifications in a search condition in a subquery can reference columns of the table specified outside the subquery. For nested queries, referencing a table or column that is specified in an outside query from an inner query is called an outer reference.
When qualifying a column specification in a search condition with a table name, table columns for which a correlation name is specified must be qualified with a correlation name.
COUNT(*), COUNT_FLOAT(*), and window functions cannot be specified in a subquery's ON search condition. A set function can be specified in an ON search condition only in the ON search condition in a FROM clause belonging to a HAVING clause. If a set function is specified in an ON search condition in a FROM clause that belongs to a HAVING clause, any table specification in the set function must reference (in an outer reference) the table that was specified in a FROM clause preceding the HAVING clause.

Specifies a table subquery. The table derived by this query is called a derived table of the FROM clause. The n-th column in the table subquery becomes the n-th column in the derived table.

The query specification containing a derived table is a read-only specification.

When specifying a derived table, observe the following notes:

[AS] correlation-name
Specifies the name of a derived table.
[(derived-column-list)]
Specifies the names of columns in a derived table.
If a derived column list is omitted, the column name derived as a result of the outermost query in the derived table becomes the column name for the derived table. Consequently, the column specification (if an AS clause is specified, the column name in the AS clause) in the selection expression will be the column name of the derived table. In other cases, HiRDB assumes a column name that is distinct from any column name used in the SQL statement.
If a derived column list is omitted, care must be taken that no columns of duplicate names are derived as a result of the table subquery.
Column names specified in a derived column list must all be distinct.
When specifying a derived column list, the number of column names used in the derived column list must be the same as the number of columns in the table that is derived by the derived table.
The number of columns used in a derived column list or derived by a table subquery must be 30,000 or less.

(4) Common Rules

If you specify the RDAREA name specification, you cannot use an index that has a different number of partitions than the table does. When defining an index for a query that specifies an RDAREA name, define an index with the same number of partitions as the table has.

(5) Rules on joined tables

  1. Specifying an outer join permits the specification of the null value in the columns of the inner table that results from the outer join.
  2. Only columns in an outer or inner table or outer reference columns can be specified in ON search-condition.
  3. Joined tables are read-only tables.
  4. Tables derived from an inner join are composed of concatenated rows that satisfy the ON search condition for the outer and inner tables.
  5. Tables derived from an outer join are composed of concatenated rows that satisfy the ON search condition for the outer and inner tables, and rows to which the null value is added, in numbers equal to the columns in the inner table, to the rows that do not satisfy the ON search condition for the outer table.
  6. For a query specification containing a joined table, ROW cannot be specified in the selection expression that is derived from the inner table of the outer join.

(6) Rules on derived tables in a FROM clause

  1. ROW cannot be specified in a selection expression in a derived table in a FROM clause.
  2. An unsubscripted repetition column cannot be specified in a selection expression for a derived table in a FROM clause.
  3. WRITE or GET_JAVA_STORED_ROUTINE_SOURCE cannot be specified in a selection expression for a derived table in a FROM clause.
  4. A format that can omit [AS]correlation-name[(derived-column-list)] (SELECT COUNT(*) FROM derived-table or SELECT COUNT_FLOAT(*) FROM derived-table) cannot be specified in a WITH clause query, view definition, set operation, INSERT statement, or subquery.

(7) Notes

  1. The scope for correlation names specified in a FROM clause, tables names specified without a correlation name, or query names is the innermost subquery including the FROM clause, the query specification, or the single-row SELECT statement. The scope also includes subqueries that are interior to these elements.
  2. If a correlation name is specified in a FROM clause in a subquery, the table name or query name does not have a valid scope.
  3. The valid scope of a table name or a query name that specifies a correlation name in the outermost query specification, or that specifies a correlation name in the FROM clause directly under a single-row SELECT statement, consists of the query specifications other than the innermost subquery, or the single-row SELECT statement. However, if a specified correlation name is the same as a table name or a query name in the FROM clause, the table name or query name in the FROM clause that specifies the correlation name does not have a valid scope.
    The following figures show examples of scopes of correlation names, table names, and query names.
    If a table name or query name specified in a FROM clause in a subquery is associated with a correlation name, the table name or query name does not have a valid scope (T3 in the figure). If a table (T2 X in the figure) with a correlation name that is directly specified in the query is referenced by an inner query, the column name must be qualified with a column name (X), rather than with a table name (T2).

    Figure 2-1 Examples of valid scopes for correlation names, table names, and query names

    [Figure]
    If a correlation name, table name, or query name is referenced within the scope in which the same valid name is specified more than once, the one with the greatest local valid scope is specified. To reference an outer table in such a situation, specify a different correlation name in the FROM clause, and use that name to specify the reference.

    Figure 2-2 Examples of valid scopes of correlation names, table names, and query names (when a specified correlation name is the same as a table name or a query name in a FROM clause)

    [Figure]
    If a specified correlation name is the same as a table name or a query name in the FROM clause, the table name or query name in the FROM clause that specifies the correlation name does not have a valid scope.
  4. The following figure shows an example of the scopes for correlation names and table names with a FROM derived table specified.

    Figure 2-3 Example of valid scopes for correlation names and table names with a FROM derived table specified

    [Figure]