7.11.1 Specification format for table references

A table reference, specified in the FROM clause, specifies the table from which to retrieve data.

If you want to retrieve data from a table that is joined to itself, a correlation name can also be specified.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Examples

(1) Specification format

table-reference::= {table-primary|joined-table}
 
  table-primary::= {table-name [time-travel-specification] 
                               [[AS] correlation-name]
                               [index-specification]
             |query-name [[AS] correlation-name]
             |derived-table [[AS] correlation-name [(derived-column-list)]]
             |table-function-derived-table [[AS] correlation-name]
                                           [{(table-function-column-list)
                                            |(derived-column-list)}]
             |collection-derived-table [AS] correlation-name [(derived-column-list)]
             |(joined-table)]
 
    time-travel-specification::= FOR {VERSION AS OF snapshot-ID-specification
                                     |TIMESTAMP AS OF timestamp-specification}
    derived-table::= [table-subquery|(table-value-constructor)]
    table-function-derived-table::= {TABLE(system-defined-function)
                                    |system-defined-function}
    collection-derived-table::= UNNEST(column-specification[,column-specification]…)
 
    derived-column-list::= column-name[,column-name]…
    table-function-column-list::= column-name data-type[,column-name data-type]…

To Page Top

(2) Explanation of specification format

table-name:

Specifies the table from which to retrieve data. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

To retrieve data from a dictionary table or system table, specify the schema name MASTER.

If an archivable multi-chunk table is specified, accesses to the location table and system table (STATUS_CHUNKS) occur. At this time, locked resources are secured for the system table (STATUS_CHUNKS). For details about locks, see Locking in the HADB Setup and Operation Guide.

time-travel-specification:

time-travel-specification::= FOR {VERSION AS OF snapshot-ID-specification
                                 |TIMESTAMP AS OF timestamp-specification}

Specifies when retrieving foreign data in a past state (snapshot) from a foreign table that references an Iceberg table. Specify which snapshot to use by snapshot ID or timestamp specification. The schema and foreign data corresponding to the specified snapshot are loaded into the foreign table.

Important: The time travel specification can be specified only when the table name specifies a foreign table defined by specifying ICEBERG for the foreign data format specification in the CREATE FOREIGN TABLE statement.

snapshot-ID-specification

Specifies the snapshot ID of the snapshot to be retrieved. Specify an unsigned integer literal of 1 or greater for the snapshot ID specification.

timestamp-specification

Specifies when you want to determine the snapshot to be retrieved based on a date and time. The most recent snapshot on or before the date and time specified by the timestamp specification (reference date and time) will be retrieved. Specify either of the following literals for the timestamp specification:

Time stamp literal with time zone where fractional seconds precision is 0 or 3

Example:
```
TIMESTAMP'2025-12-30 20:03:58+09:00'
```
Character string literal in predefined input representation for time stamps with time zone where fractional seconds precision is 0 or 3

Example:
```
'2025-12-30 20:03:58+09:00'
```

In the above example, 11:03:58 on December 30, 2025 in UTC becomes the reference date and time, and the most recent snapshot on or before the reference date and time will be retrieved.

Note the following points:

If a snapshot cannot be obtained, such as when a snapshot that meets the conditions specified in the time travel specification does not exist, the SQL statement results in an error.
The column definitions of the foreign table (definitions such as column names and column data types) are generated using the metadata corresponding to the snapshot determined by the time travel specification. The column definitions of the foreign table are generated according to the rules described in (a) If ICEBERG is specified in the foreign data format specification in (5) Rules for omitting the column definition specification in 3.6.1 Specification format and rules for the CREATE FOREIGN TABLE statement.
The information about the column definitions of the foreign table stored in the dictionary tables (SQL_TABLES table, SQL_COLUMNS table, and SQL_DATATYPE_DESCRIPTORS table) is from the time when the foreign table was defined (not from the time when the foreign table was retrieved). When retrieving the foreign table, the column definitions of the foreign table are generated using the metadata corresponding to the snapshot determined by the time travel specification. Therefore, if there is a change in the metadata schema, the information about the column definitions of the foreign table stored in the dictionary tables and the column definitions of the foreign table generated when retrieving the foreign table will be inconsistent.

[AS] correlation-name:

Specifies a name assigned to separately identify a table for one of the following purposes:

To join a table to itself
To reference a column of the same table inside a subquery

Note the following points:

A correlation name is required when making the following specifications:
- A table function derived table that specifies the ADB_AUDITREAD function or the ADB_CSVREAD function
- A collection derived table
To specify the same scope variable multiple times in one FROM clause, specify correlation names so that each scope variable is able to uniquely identify the column specification it qualifies.
The correlation name specified in one FROM clause must be different from all scope variables specified in that clause. The name must also be different from the table identifiers of the scope variables. For details about the effective scope of scope variables, see 6.8 Scope variables.
The retrieval results will be the same regardless of whether AS is specified.
If the correlation name of a derived table or a table function derived table is not specified, the correlation name is automatically assigned according to the following naming rules:

##ADD_DRVTBL_xxxxxxxxxx

In the preceding format, xxxxxxxxxx is a 10-digit integer. This correlation name is displayed in the access path execution results.

For table references with the same effective scope, do not specify a table name or correlation name that begins with ##ADD_DRVTBL_.

Note

If you do so, the HADB server might automatically assign a correlation name that is the same as a name that you specified.

index-specification:

Specifies a B-tree index or text index to be used when retrieving from a base table. Alternatively, it specifies that the use of a B-tree index or text index is to be suppressed. For details about index specifications, see 7.14 Index specification.

query-name:

Specifies a query name. For details about query names, see (a) WITH-clause in (2) Explanation of specification format in 7.1.1 Specification format and rules for query expressions.

derived-table:

Specifies a derived table in the format of a table subquery or table value constructor. For details about subqueries, see 7.3 Subqueries. For details about table value constructors, see 7.17 Table value constructors.

A derived table is a table that is derived as a result of a table subquery or table value constructor. The n-th column of a table subquery or table value constructor becomes the n-th column of a derived table.

A query specification that contains a derived table will be converted to an equivalent query specification that does not contain the derived table.

Assume that the (user-specified or automatically assigned) correlation name of the derived table is the derived query name, and the query expression of the derived table is the derived query expression. In this case, the derived query expression is treated as an internal derived table, following the rules for derived table expansion. For the rules for derived table expansion, see 7.33 Internal derived tables.

In addition, note the following concerning derived-table:

If the correlation name of a derived table is not specified, the scope variable of that derived table has the effective scope but has no name (the HADB server internally generates a name, which users cannot check). Therefore, if there are two or more table references that have the same column name in the same effective scope, explicitly specify correlation names.

Example of an SQL statement that generates an error:
```
SELECT "C1" FROM "T1",(SELECT "C1" FROM "T1")
```
For the underlined column (C1), there are multiple table references that have the same column name in the same effective scope (table T1 and the derived table). In this case, it is impossible to identify whether the underlined column (C1) is column C1 of table T1 or column C1 of the derived table. Therefore, the SQL statement will result in an error. In such a case, to reference a column of a derived table, specify a correlation name for the derived table, and qualify the column name with that correlation name. The following shows examples.

Example of a correct SQL statement:
```
SELECT "DT1"."C1" FROM "T1",(SELECT "C1" FROM "T1") AS "DT1"
```
You cannot specify the row interface (ROW) for a derived table.

derived-column-list:

Specify the column names of each column of the derived table, table function derived table, or collection derived table. Specify derived-column-list in the following format:

column-name[, column-name]...

The column names of the table derived vary depending on whether derived-column-list is specified or whether derived-column-list is not specified. For the rules concerning derived column names, see 6.9 Derived column names.

In addition, note the following concerning derived-column-list:

If derived-column-list is omitted, the column names derived must be unique.
The column names in derived-column-list must be unique.
Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in derived-column-list. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.
If derived-column-list is specified, the number of column names in derived-column-list must be the same as the number of columns in the derived table, table function derived table, or collection derived table.
Make sure that the number of columns specified in derived-column-list does not exceed 4,000. In addition, make sure that the number of columns in the table derived by the derived table, table function derived table, or collection derived table does not exceed 4,000.
The derived column list cannot be specified for a table function derived table that specifies the ADB_AUDITREAD function or the ADB_CSVREAD function.

table-function-derived-table:

table-function-derived-table::={TABLE(system-defined-function)
                               |system-defined-function}

A table function derived table is a collection of data in table format derived by means of a system-defined function. For details about system-defined functions, see 7.15 System-defined functions.

The rules for specifying a table function derived table are as follows:

Specify a table function derived table in either TABLE(system-defined-function) or system-defined-function format.
- If the ADB_AUDITREAD function or the ADB_CSVREAD function is specified as the system-defined function, specify it in the format TABLE(system-defined-function).
- If the ADB_GENERATE_SERIES function is specified as the system-defined function, specify it in the format system-defined-function.
You cannot specify the row interface (ROW) for a table function derived table.

table-function-column-list:

table-function-column-list::=column-name data-type[,column-name data-type]...

Specifies the name and data type of each column in the table function derived table.

The rules for specifying a table function column list are as follows:

If you specify the ADB_AUDITREAD function for a table function derived table, you cannot specify a table function column list.
If you specify the ADB_CSVREAD function for a table function derived table, you must specify a table function column list.
If you specify the ADB_GENERATE_SERIES function for a table function derived table, the table function column list cannot be specified.
For the specification format of each data type, see 6.2.1 List of data types.
The column names in a table function column list must be unique.
Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in a table function column list. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.
The number of columns in a table function column list must not exceed 4,000.
You cannot specify VARCHAR or STRING type data whose length exceeds 32,000 bytes for a table function column list.
Array and STRUCT type data cannot be specified for the data type of the table function column list.
If the integer data type format is in a legacy format, and the data type of the table function column list is specified as BIGINT, HADB assumes that the INTEGER type is specified.
If a table function column list data type of type NUMERIC is specified, HADB assumes that type DECIMAL is specified.
If a FLOAT type is specified as the data type for a table function column list, HADB assumes that a DOUBLE PRECISION type is specified.

For rules on derived column names, see (4) In the case of a table function derived table in 6.9.2 Decision rules for derived column names in query results.

collection-derived-table:

collection-derived-table:: = UNNEST (column-specification[,column-specification]...)

Specify the column specification in the collection derived table. A row is generated with the column value of each array element of the array data specified in the column specification, and the set of rows is the result of the collection derived table. The n-th column of the result of a collection derived table becomes the n-th column of a collection derived table. For details about column specifications, see (5) Column specification format in 6.1.5 Qualifying a name.

Note the following rules:

Specify an array-type column for the column specification.
Make sure that the number of column specifications does not exceed 4,000.
Specify for the column specification a column in the base table or foreign table that satisfies all of the following conditions.
- Base table or foreign table specified in the same FROM clause as the collection derived table
- Base table or foreign table that is comma joined with the collection derived table
- Base table or foreign table specified to the left of the collection derived table
If multiple column specifications are specified, columns of different tables cannot be specified.

The result of the comma join of the table specified on the left-hand side of the collection derived table and the collection derived table will be a set of rows that are concatenated by combining each row of the result of the collection derived table with each row of the table specified on the left-hand side.

Example:

[Figure]

In this case, the result of the collection derived table is derived in the following flow.

For each array element of the array data referenced by the i-th column specification, a row is created with i-th column value for the number of array elements of the array data with the largest number of array elements in the column specification. At this time, the following rule is applied.
- N_max is the maximum number of array elements in the column specification. If the number of array elements is N_j, which is the number array elements in the jth column specification is less than N_max, the array elements in the "N_j+1 to N_max" range of the array data in the jth column specification are filled with null values.
- If the array data referenced by all column specifications is null or empty array data, no rows are generated.
The result of the collection derived table will be the union of the generated rows.

(Example) "T1", UNNEST("T1"."" C1", "T1"."" C2") is specified

Columns C1 and C2 are array-type columns with element data type INTEGER and maximum number of elements of 10.

The following is an example of the result of comma-joining a table specified on the left-hand side of a collection derived table with a collection derived table.

The table T1 used in the column is as follows.

[Figure]

When there is only one column specification specified in the collection derived table
Example 1:
```
FROM "T1",UNNEST("T1"."C2") AS "T2"("DC2")
```
The underlined portion indicates the collection derived table.

<Result of the above FROM clause>
Example 2:
```
FROM "T1",UNNEST("T1"."C3") AS "T2"("DC3")
```
The underlined portion indicates the collection derived table.

<Result of the above FROM clause>
Example 3:
```
FROM "T1",UNNEST("T1"."C2") AS "T2"("DC2"),UNNEST("T1"."C3") AS "T3"("DC3")
```
The underlined portion indicates the collection derived table.

<Result of the above FROM clause>
When there are multiple column specifications specified in the collection derived table
Example:
```
FROM "T1",UNNEST("T1"."C2","T1"."C3") AS "T2" ("DC2","DC3")
```
The underlined portion indicates the collection derived table.

<Result of the above FROM clause>

joined-table:

Specifies a joined table. For details on joined tables, see 7.12 Joined tables.

To Page Top

(3) Examples

The following examples illustrate table references.

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased products on or after September 6, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-06'

The underlined portion indicates the table reference.

Example 2

Search the dictionary table (SQL_INDEXES) to find the names of indexes (INDEX_NAME) that are defined for the sales history table (SALESLIST).

SELECT "INDEX_NAME"
    FROM "MASTER"."SQL_INDEXES"
        WHERE "TABLE_NAME"='SALESLIST'

The underlined portion indicates the table reference. To search the dictionary table, you must qualify the table name with the schema name MASTER.

Example 3

Search the dictionary table (SQL_INDEXES) to find the names of indexes (INDEX_NAME) that are defined for the sales history table (SALESLIST). IDX is used as the correlation name.

SELECT "IDX"."INDEX_NAME"
    FROM "MASTER"."SQL_INDEXES" AS "IDX"
        WHERE "IDX"."TABLE_NAME"='SALESLIST'

The underlined portion indicates the table reference.

Example 4

Retrieve the customer ID (USERID), product code (PUR-CODE), customer name (NAME), and sex (SEX) from the sales history table (SALESLIST) and customer table (USERSLIST), joined together with the customer ID column (USERID) as the search condition.

SELECT "SALESLIST"."USERID","PUR-CODE","NAME","SEX"
    FROM ("SALESLIST" JOIN "USERSLIST"
           ON "USERSLIST"."USERID"="SALESLIST"."USERID")

The underlined portion indicates the table reference.

Example 5

Extract the following data from a CSV file (/dir/file.csv.gz) compressed in GZIP format:

Customer ID (USERID)
Customer name (NAME)
Age (AGE)

SELECT "USERID","NAME","AGE"
    FROM TABLE(ADB_CSVREAD(MULTISET ['/dir/file.csv.gz'],
                          'COMPRESSION_FORMAT=GZIP;'))
         AS "USERLIST" ("USERID" CHAR(5),
                        "NAME" VARCHAR(100),
                        "AGE" INTEGER,
                        "COUNTRY" VARCHAR(100),
                        "INFORMATION" VARBINARY(10))

The underlined portion indicates the table reference.

Example 6

Search the interest list column (INTEREST-LIST) of the customer table (USERSLIST) for the customer ID (USERID) of the customer interested in fashion.

The interest list column (INTEREST-LIST) is an array-type column whose array elements contain information about the areas of interest to the customer.

SELECT "USERID"
    FROM "USERSLIST",UNNEST("INTEREST-LIST") "CDT"("INTEREST")
        WHERE "CDT"."INTEREST" = 'fashion'

The underlined portion indicates the table reference.

Example 7

Table IT1 is a foreign table defined by specifying ICEBERG for the foreign data format specification in the CREATE FOREIGN TABLE statement. It loads and retrieves past foreign data (snapshots) into the foreign table by specifying the time travel specification.

When specifying a snapshot ID:
```
SELECT * FROM "IT1" FOR VERSION AS OF 1234567890123
```
In this case, the snapshot with snapshot ID 1234567890123 is used.

The underlined portion indicates the time travel specification for table reference.
When specifying a time stamp:
```
SELECT * FROM "IT1" FOR TIMESTAMP AS OF TIMESTAMP'2025-10-01 10:00:00+09:00'
```
In this case, the latest snapshot before 1:00 AM UTC on October 1, 2025, is used.

The underlined portion indicates the time travel specification for table reference. A time stamp with time zone literal is specified for the time stamp.
```
SELECT * FROM "IT1" FOR TIMESTAMP AS OF '2025-10-01 10:00:00+09:00'
```
In this case, the latest snapshot before 1:00 AM UTC on October 1, 2025, is used.

The underlined portion indicates the time travel specification for table reference. A character string literal in the predefined input representation for time stamps with time zone is specified for the time stamp.

To Page Top