7.12.1 Specification format and rules for joined tables

(1) Specification format

joined-table ::= {cross-join|qualified-join|(joined-table)}
 
 
  cross-join ::= table-reference CROSS JOIN table-primary
 
  qualified-join ::= table-reference [{INNER|{LEFT|RIGHT|FULL} [OUTER]}] JOIN [join-method-specification] table-reference join-specification
    join-specification ::= ON search-condition

To Page Top

(2) Explanation of specification format

• cross-join

cross-join ::= table-reference CROSS JOIN table-primary

Specify this to obtain the Cartesian product of the table-reference specified on the left side and the table-primary specified on the right side. For details about table references, see 7.11 Table reference. For details about table-primary, see 7.11.1 Specification format for table references.

Note that when * is specified in the selection expression of a query specification, the columns in the retrieval results will be arranged according to the order of the columns from table-reference on the left first and then the columns from table-primary on the right.

The cross join is illustrated in the following example.

Example:

SELECT statement to be executed

SELECT * FROM "USERSLIST" CROSS JOIN "SALESLIST"

Retrieval results

Each row in USERLIST is combined with every row in SALESLIST.

• qualified-join

qualified-join ::= table-reference [{INNER | {LEFT | RIGHT | FULL} [OUTER]}] JOIN [join-method-specification] table-reference join-specification
  join-specification ::= ON search-condition

Specify this to perform an inner join or outer join.

When INNER JOIN is specified, the operation is called an inner join, and when LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN is specified, the operation is called an outer join.

table-reference:

Specifies a table or a joined table to be joined. For details about table references, see 7.11 Table reference.

When * is specified in the selection expression of a query specification, the columns in the retrieval results will be arranged according to the order of the columns from the table references on the left first and then from the table references on the right. This is illustrated in the following example.

Example:

[INNER] JOIN:

The joined table will consist of the rows in the Cartesian product of the tables referenced by the left and right table references for which the search-condition specified in join-specification is true.

For an example of INNER JOIN, see 7.12.2 Inner join using INNER JOIN.

LEFT [OUTER] JOIN:

The joined table will be the union of the following rows:

• The rows in the Cartesian product of the tables referenced by the left and right table references for which the search-condition specified in join-specification is true (the same results as when INNER JOIN is specified).

• The rows in the Cartesian product of the tables referenced by the left and right table references such that search-condition is false for the rows of the left table and the rows of the right table are assigned null values.

For an example of LEFT OUTER JOIN, see 7.12.3 Outer join using LEFT OUTER JOIN.

RIGHT [OUTER] JOIN:

The joined table will be the union of the following rows:

• The rows in the Cartesian product of the tables referenced by the left and right table references for which the search-condition specified in join-specification is true (the same results as when INNER JOIN is specified).

• The rows in the Cartesian product of the tables referenced by the left and right table references such that search-condition is false for the rows of the right table and the rows of the left table are assigned null values.

For an example of RIGHT OUTER JOIN, see 7.12.4 Outer join using RIGHT OUTER JOIN.

FULL [OUTER] JOIN:

The joined table will be the union of the following rows:

• The rows in the Cartesian product of the tables referenced by the left and right table references for which the search-condition specified in join-specification is true (the same results as when INNER JOIN is specified).

• The rows in the Cartesian product of the tables referenced by the left and right table references such that search-condition is false for the rows of the left table and the rows of the right table are assigned null values.

• The rows in the Cartesian product of the tables referenced by the left and right table references such that search-condition is false for the rows of the right table and the rows of the left table are assigned null values.

For an example of FULL OUTER JOIN, see 7.12.5 Outer join using FULL OUTER JOIN.

join-method-specification:

Specifies the method for joining the left and right table references. For details, see 7.13 Join method specification.

Note that join-method-specification does not normally need to be specified. If join-method-specification is omitted, HADB determines the join method automatically.

join-specification:

join-specification ::= ON search-condition

Specifies the conditions for joining the two table references.

ON search-condition:

Specifies a search condition. For details on search conditions, see 7.18 Search conditions.

Each column specification in the search condition must be one of the following:

• A column included in the two table references being joined

• An external reference column

For details about external reference columns, see (a) Common rules for subqueries in (4) Rules in 7.3.1 Specification format and rules for subqueries.

If you qualify a column name from search-condition in a table specification, any column from a table with a correlation name must be qualified with the correlation name.

Subqueries are not permitted in the ON search condition of a joined table with FULL OUTER JOIN specified as the joined table mode.

• (joined-table)

To specify the join order of the tables, enclose the joined tables in parentheses.

To Page Top

(3) Rules

1. When LEFT OUTER JOIN is specified, the NOT NULL constraint does not apply to the results of the right table reference (null values are allowed).

2. When RIGHT OUTER JOIN is specified, the NOT NULL constraint does not apply to the result of the left table reference (null values are allowed).

3. When FULL OUTER JOIN is specified, the NOT NULL constraint does not apply to the results of both the left and right table references (null values are allowed).

4. Up to 63 outer joins with FULL OUTER JOIN specified as the joined table mode can be specified in an SQL statement. If the table reference to be joined is a viewed table, an internal derived table is generated according to the query expression specified in the CREATE VIEW statement. The limit on the maximum number of FULL OUTER JOIN clauses is applied to this internal derived table.

5. If FULL OUTER JOIN is specified, a derived table is generated. For the derived table, the HADB server automatically assigns a correlation name in the following format:

   ##DRVTBL_xxxxxxxxxx

   In the preceding format, xxxxxxxxxx is a 10-digit integer.

6. If hash join is selected as the table joining method and a hash filter is applied to hash join processing, a hash filter area of an appropriate size is required. The size of the hash filter area is specified by using the adb_sql_exe_hashflt_area_size operand in the server definition or client definition.

7. The HADB server sometimes converts INNER JOIN or CROSS JOIN to an equivalent comma join when executing an SQL statement. For details about a comma join, see (2) Explanation of specification format in 7.5.1 Specification format and rules for FROM clauses.

To Page Top

(4) Examples

Example 1 (example of INNER JOIN)

From the customer table (USERSLIST) and sales history table (SALESLIST), retrieve a list of customers (customer ID and name) who purchased product code (PUR-CODE) P001, eliminating duplicates.

SELECT DISTINCT "USERSLIST"."USERID","NAME"
    FROM "USERSLIST" INNER JOIN "SALESLIST"
            ON "USERSLIST"."USERID"="SALESLIST"."USERID"
        WHERE "SALESLIST"."PUR-CODE"='P001'

The underlined portion indicates the joined table (inner join).

Example 2 (example of LEFT OUTER JOIN)

From the product table (PRODUCTLIST) and sales history table (SALESLIST), determine the total number of sales in December 2012 for each product.

SELECT "PRODUCTLIST"."PUR-NAME",SUM("SALESLIST"."PUR-NUM") AS "SUM"
    FROM "PRODUCTLIST" LEFT OUTER JOIN "SALESLIST"
       ON "PRODUCTLIST"."PUR-CODE"="SALESLIST"."PUR-CODE"
       AND "SALESLIST"."PUR-DATE" BETWEEN DATE'2012-12-01'
                                      AND DATE'2012-12-31'
    GROUP BY "PRODUCTLIST"."PUR-NAME"

The underlined portion indicates the joined table (outer join).

Example 3 (example of RIGHT OUTER JOIN)

From the product table (PRODUCTLIST) and sales history table (SALESLIST), determine the total number of sales in December 2012 for each product.

SELECT "PRODUCTLIST"."PUR-NAME",SUM("SALESLIST"."PUR-NUM") AS "SUM"
    FROM "SALESLIST" RIGHT OUTER JOIN "PRODUCTLIST"
       ON "SALESLIST"."PUR-CODE"="PRODUCTLIST"."PUR-CODE"
       AND "SALESLIST"."PUR-DATE" BETWEEN DATE'2012-12-01'
                                      AND DATE'2012-12-31'
    GROUP BY "PRODUCTLIST"."PUR-NAME"

The underlined portion indicates the joined table (outer join).

Example 4 (example of FULL OUTER JOIN)

From the customer table (USERSLIST), product table (PRODUCTLIST), and sales history table (SALESLIST), retrieve a list of combinations of customer name and product name for the customers who bought products in December 2012, eliminating duplicates.

• For customers without a purchase record, use the null value for the product name (PUR-NAME).

• For products with 0 sales, use the null value for the customer name (NAME).

SELECT DISTINCT "USERSLIST"."NAME","PRODUCTLIST"."PUR-NAME"
    FROM ("USERSLIST" LEFT OUTER JOIN "SALESLIST"
                        ON "USERSLIST"."USERID"="SALESLIST"."USERID"
                        AND "SALESLIST"."PUR-DATE" BETWEEN DATE'2012-12-01'
                                                       AND DATE'2012-12-31')
                      FULL OUTER JOIN "PRODUCTLIST"
                        ON "SALESLIST"."PUR-CODE"="PRODUCTLIST"."PUR-CODE"

The underlined portion indicates the joined table (outer join).

Example 5: (Specifying a subquery in the search condition of a join specification)

From the product table (PRODUCTLIST) and sales history table (SALESLIST), produce a list of customers who purchased the greatest quantity of each product. In the case of products with no sales, the customer ID (USERID) and quantity purchased (PUR-NUM) are assigned null values.

SELECT "G"."PUR-NAME","S"."USERID","S"."PUR-NUM"
    FROM "PRODUCTLIST" "G" LEFT JOIN "SALESLIST" "S"
       ON "G"."PUR-CODE"="S"."PUR-CODE"
           AND "S"."PUR-NUM"=(
                              SELECT MAX("SMAX"."PUR-NUM")
                                FROM "SALESLIST" "SMAX"
                                  WHERE "S"."PUR-CODE"="SMAX"."PUR-CODE"
                             )

The underlined portion indicates the joined table.

To Page Top