7.5.1 Specification format and rules for FROM clauses : Hitachi Advanced Database SQL Reference

(1) Specification format

FROM-clause ::= FROM table-reference[, table-reference]...

To Page Top

(2) Explanation of specification format

table-reference:

Specifies the tables from which to retrieved data in the form of a table reference. For details about table references, see 7.11 Table reference.

When you perform a query across multiple table references (a query containing multiple table names, query names, derived tables, or table function derived tables in the FROM clause), it is called a table join.

Also, when you perform a join by using a comma-separated list of multiple table references, it is called a comma join.

To Page Top

(3) Rules

A total of 64 table names, query names, derived tables, and table function derived tables can be specified in all table references in a FROM clause. The table specification counts are computed as follows.
- When a table name is specified in a table reference: 1
- When a derived table is specified in a table reference: 1
- When a joined table is specified in a table reference: the total number of table names and derived tables specified in the joined table
- When a query is specified in a WITH clause: 1
- When a table function derived table is specified in a table reference: 1
The following shows an example of computing the table specification counts.

Example:
```
WITH "Q1" AS (SELECT * FROM "T6","T7")
SELECT * FROM "T1",                                       ...[a]
    "T2" LEFT OUTER JOIN "T3" ON "T2"."C1"="T3"."C1",     ...[b]
    (SELECT * FROM "T4","T5") "W1",                       ...[c]
    "Q1",                                                 ...[d]
    TABLE(ADB_CSVREAD(MULTISET['/dir/file.csv.gz'],
                     'COMPRESSION_FORMAT=GZIP;'))
    AS W2 (C1 INT)                                        ...[e]
```
[Explanation]
1. Table name T1 is specified. Here, therefore, the number of table names is 1.
2. A joined table is specified, and two table names (T2 and T3) are specified in the joined table. Here, therefore, the number of table names is 2.
3. A derived table (W1) is specified. Here, therefore, the number of derived tables is 1.
4. Query name Q1 that is specified in the WITH clause is specified. Here, therefore, the number of query names is 1.
5. A table function derived table is specified. Here, therefore, the number of derived tables is 1.
As described earlier, in the preceding example, the number of table names specified in all table references is six in total.
The column descriptors used for the results of the FROM clause will be the same as the column descriptors from the tables specified in the FROM clause. In addition, the order of the columns in the result of the FROM clause will be the order of the columns in the tables specified in the FROM clause. For example, consider what happens when the SELECT statement shown below is executed.

Example:
```
SELECT * FROM "T1","T2"
```
Assume that columns C1 and C2 are defined in table T1, and columns C3 and C4 are defined in table T2. In this case, the order of columns in the results of the FROM clause is as follows: C1 → C2 → C3 → C4

Note

A column descriptor contains attribute information for a column. It consists of the column's name, data type, data length, column ID (numbered from the first column), and whether it contains null values.

To Page Top

(4) Example

The following example illustrates a FROM clause.

Example

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 product code P002 on or after September 6, 2011.

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

The underlined portion indicates the FROM clause.

To Page Top

(5) Notes

If you specify multiple table references in the FROM clause in the situations listed below, a work table might be created. If the size of the work table DB area where the work table is created has not been estimated correctly, it might result in performance degradation. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide.

When multiple table references are specified in a single FROM clause
When the same viewed table name is specified in more than one place in a single SQL statement
When the query name specified in a WITH clause is specified in more than one place in a single SQL statement

For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.

To Page Top