table-reference::= {table-primary | joined-table}
table-primary::=
{[[RD-node-name.]authorization-identifier.] table-identifier
[[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
{[[RD-node-name.]authorization-identifier.]
table-identifier [[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)]]}
- RD-node-name
- Specifies the name of the RD node to be accessed.
- 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.
- 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 specifed 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.
- joined-table::= table-reference [{INNER |LEFT [OUTER]}] JOIN
[join-method-SQL-optimization-specification]
table-reference ON search-condition
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 should be qualified with a correlation name.
- COUNT(*) or the window function cannot be specified as the ON search condition in a subquery. 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 should reference (in an outer reference) the table that was specified in a FROM clause preceding the HAVING clause.
- derived-table [AS] correlation-name [(derived-column-list)]]
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:
- If a derived table is specified for table reference, a correlation name for the derived table should be specified. The specification [AS] correlation-name [(derived-column-list)]] can be omitted only if the following format is specified in the outermost query:
SELECT COUNT(*) FROM derived-table
- The following structure or data type cannot be specified as a constituent column of a table which is derived as a result of a table subquery in which a value expression is specified in a selection expression:
Repetition structure
BLOB
BINARY with a minimum length of 32,001 bytes
BOOLEAN
Abstract data type
- A row interface (ROW) cannot be specified on a derived table.
- The window function cannot be specified in a derived table.
- [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 should 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 should 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.