2.5 Table expressions

Organization of this section
(1) Function
(2) Format
(3) Operands
(4) Common rules
(5) Join rules
(6) Rules for the GROUP BY clause and HAVING clause
(7) Notes
(8) Specification example: Specifying a table expression in a dynamic SELECT statement
(9) Usage examples

(1) Function

A table expression specifies one or more tables to be retrieved, tables to be inner-joined or outer-joined, or the query name specified in a WITH clause of a query expression. The conditions under which a table is to be retrieved or joined (search conditions or grouping conditions) can also be specified in a table expression. A table expression is specified in either a subquery or a query specification or in a single-line SELECT statement.

(2) Format

FROM table-reference [,table-reference]...
 [WHERE search-condition]
 [GROUP BY value-expression [,value-expression]...]
 [HAVING search-condition]

(3) Operands

(a) Not using a subquery

Specifies the table, query name, derived table, or joined table to be retrieved. For details about table references, see 2.6 Table reference.

Omitting a search condition causes the system to retrieve all rows that are derived from a table (a specified table, joined table, derived table, or a table that is derived as a derived query expression in a WITH clause).

Embedded variables can be specified in a search condition. In a SELECT statement prepared by the PREPARE statement, ? parameters are used in place of embedded variables.

Either an SQL variable or an SQL parameter is used in the SQL procedure. For details about Java procedures, see the section on the JDBC driver in the HiRDB Version 9 UAP Development Guide.

Specifies grouping. In a grouping operation, all rows that have the same value in the result of the value expression specified by the GROUP BY clause are treated as a group and are output as one row.

Only the following can be specified in a selection expression:column names used for grouping, set functions, literals, value expressions that include these items as primaries, and value expressions to be grouped. In other words, value expressions that include value expressions to be grouped as primaries (except when value expressions are column specifications) cannot be specified. A value expression specified by a GROUP BY clause is called a grouping column.

The following rules apply to the GROUP BY clause:

  1. If the columns that serve as a grouping condition contain a row that has null values, all the null values are treated alike and grouping is performed accordingly.
  2. Duplicate value expressions of the same format cannot be specified in a value expression specified in a GROUP BY clause.
  3. A set function cannot be included in a value expression specified in a GROUP BY clause.
  4. The window function cannot be included in a value expression specified in a GROUP BY clause.
  5. A component specification cannot be included in a value expression specified in a GROUP BY clause.
  6. A repetition column cannot be specified in a value expression specified in a GROUP BY clause.
  7. A subquery cannot be contained in a value expression specified in the GROUP BY clause.

A set function (AVG, MAX, MIN, SUM, COUNT or COUNT_FLOAT) can be specified.

The following rules apply to the HAVING clause:

  1. Only grouped value expressions can be specified in a search condition other than a set function.
  2. Any grouped value expression must have a format exactly identical to the value expression that is specified in the GROUP BY clause.
  3. If a search condition is omitted, all groups are output.
(b) Using a subquery

Specifies the table, query name, derived table, or joined table to be retrieved. For details about table references, see 2.6 Table reference.

If tables are added to the FROM clause, the rows that are taken from the tables, one row per table, and joined in the order in which the tables are specified, become the rows of the table that is the result of the FROM clause. The number of rows in the resulting table is the product of the numbers of rows in the original tables.

A column specification in a search condition in a subquery can reference the columns of the tables specified outside the subquery.

In the case of nested queries, referencing from an inner query to (a column in) a table specified by an outer query is called outer referencing.

The following rules apply to the WHERE clause:

  1. COUNT(*) and COUNT_FLOAT(*) cannot be specified in the WHERE clause.
  2. A set function can be specified in a WHERE clause only if the WHERE clause belongs to a HAVING clause.
  3. If a set function is specified in a WHERE clause belonging to a HAVING clause, any column specification in the set function must reference (in an external reference) the table that is specified in the FROM clause preceding the HAVING clause.
  4. In a subquery in a WHERE clause with a query specification in which a value expression other than a column specification is specified in the GROUP BY clause, a value expression other than a column specification cannot be specified in the GROUP BY clause.

Specifies grouping. A column to be specified in a GROUP BY clause is listed in the table specified by the FROM clause in the table expression containing that GROUP BY clause.

In a grouping operation, all rows that have the same value in the result of the value expression specified by the GROUP BY clause are treated as a group and are output as one row.

Only the following can be specified in a selection expression: column names used for grouping, set functions, literals, value expressions that include these items as primaries, and value expressions to be grouped. In other words, value expressions that include value expressions to be grouped as primaries (except when value expressions are column specifications) cannot be specified. A value expression specified by a GROUP BY clause is called a grouping column.

In addition, if an item other than a column specification is specified in a value expression, the grouping column cannot be referenced from the scalar subquery specified in a selection expression.

The following rules apply to the GROUP BY clause:

  1. If the columns that serve as a grouping condition contain a row that has null values, all null values are treated alike and the grouping is performed accordingly.
  2. Duplicate value expressions of the same format cannot be specified in a value expression specified in a GROUP BY clause.
  3. A set function cannot be included in a value expression specified in a GROUP BY clause.
  4. The window function cannot be included in a value expression specified in a GROUP BY clause.
  5. A component specification cannot be included in a value expression specified in a GROUP BY clause.
  6. A repetition column cannot be specified in a value expression specified in a GROUP BY clause.
  7. A subquery cannot be contained in a value expression specified in a GROUP BY clause.

The HAVING clause specifies the condition by which groups that are obtained as a result of preceding GROUP BY, WHERE, or FROM clauses are to be selected. If a GROUP BY clause is not specified, the result of a WHERE clause or a FROM clause forms a group that does not contain any grouping columns.

The following rules apply to the HAVING clause:

  1. The group that yields the TRUE result from the search condition specified in the HAVING clause is selected.
  2. If a HAVING clause is omitted, all groups of the results of the preceding GROUP BY clause, WHERE clause, or FROM clause are selected.
  3. Any grouped value expressions must have a format exactly identical to the value expression that is specified in the GROUP BY clause.
  4. When a column specification is specified in a HAVING clause, the following provisions must be made:
    • Either reference the table in the FROM clause in the table expression, or reference the table in the FROM clause in an outer table expression (in an external reference).
    • If the table in the FROM clause in the table expression is referenced, specify the table in either a grouping column (the value expression that is specified in the GROUP BY clause) or in an argument of a set function.

(4) Common rules

  1. Embedded variables can be specified in a search condition.
  2. The ? parameter must be specified in place of an embedded variable in SQL statements that are pre-processed by the PREPARE statement.

(5) Join rules

A retrieval that specifies multiple tables or query names in a single FROM clause (a retrieval that encompasses multiple tables) is called a join.

  1. All tables or query names to be joined must be specified in the FROM clause. If no join conditions are specified in the WHERE clause, SQL extracts one row at a time from each of the tables to be joined and combines the rows to produce a result.
    Thus, if three tables composed of l rows, m rows, and n rows are joined unconditionally, l x m x n rows will result.
  2. If a condition that expresses the relationship between tables is specified in the WHERE clause (join condition), those rows that satisfy the condition are selected from the results of the concatenation operation described in (1) above.
  3. Columns that are specified to be joined in a join condition must have mutually convertible data types (one numeric value can be converted into another numeric value; one character can be converted into another character).
  4. A row that has the null value in its columns to be joined does not satisfy any condition relative to any row.
  5. When specifying a column when joining a table containing columns of the same name or a table derived as a query name from a derived query expression of the WITH clause, assign a table name or correlation name to uniquely specify the column.
  6. The total number of base tables and derived tables that can be joined in a FROM clause is 64.
    Also, the total number of base tables that can be specified in one SQL statement (including the total number of base tables specified in a subquery) is 64. The total number of correlation names that can be specified is 65.
    When using a named derived table (view table or query of a WITH clause), the number of joined tables with respect to one of the named derived tables is equal to the total number of base tables specified in the view definition statement or in a derived query expression in a WITH clause plus the number of derived tables in the FROM clause. Similarly, the number of tables specified in an SQL statement with respect to one of the named derived tables is equal to the total number of base tables specified in the view definition statement or in a derived query expression in a WITH clause. The number of correlation names specified in an SQL statement with respect to one of the named derived tables is equal to the number of correlation names specified in a view definition statement or in a derived query expression in a WITH clause.
    If a named derived table derived by specifying a set operation in a derived query expression in a view definition statement or a WITH clause is specified in an SQL statement, and the named derived table does not satisfy any of the conditions under which it can be an inner derived table, the following rules apply to the number of tables or the number of correlation names:
    number of tables =
    (total number of base tables specified in a derived query expression)
    + ((number of set operations in derived query expression + 1)
    x (total number of base tables in subquery))
    number of correlation names =
    (total number of correlation names specified in derived query expression)
    + ((number of set operations in derived query expression + 1) x (total number of correlation names in subquery))
    For details about the restrictions for a derived query expression in a view definition, see CREATE [PUBLIC] VIEW (Define view, define public view) in Chapter 3. For restrictions on derived query expressions in a WITH clause, see 2.2 Query expressions. Also, for conditions under which a named derived table becomes an inner derived table, see 2.21 Inner derived tables.
  7. In a query specification for the creation of an inner derived table, the joining of a named derived table to itself, which is the object of inner derived table creation, cannot be specified. For conditions under which an inner derived table can be created, see 2.21 Inner derived tables.

(6) Rules for the GROUP BY clause and HAVING clause

  1. For the GROUP BY clause in a view definition or a derived query expression in the WITH clause, specify a value expression with column specification.
  2. If the window function is specified for a query specification, you cannot specify a GROUP BY or HAVING clause.

(7) Notes

  1. HiRDB may create a work table when any of the following conditions is satisfied:
    • Multiple tables are joined.
    • A GROUP BY clause is specified.
    • An inner derived table is created by specifying either a view table or a query name in the table primary. (For inner derived tables, see 2.21 Inner derived tables.)
    In this process, the above processing may be subject to restrictions depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 9 Installation and Design Guide.

(8) Specification example: Specifying a table expression in a dynamic SELECT statement

SELECT SUM(SQUANTITY) FROM STOCK
  WHERE PRICE >= (SELECT AVG(PRICE)
  FROM STOCK)

(9) Usage examples

Table A is the product unit price table, table B is the table of quantities of product ordered, and table C is the table of quantities of products ordered in the last month.

[Figure]

  1. Select the product codes with a unit price greater than 200, their unit prices, and quantities ordered:

    SELECT A.PCODE, PRICE, OQUANTITY FROM A
      LEFT OUTER JOIN B
      ON A.PCODE = B.PCODE WHERE PRICE > 200

    [Figure]

    Note
    The quantity of a product for which an order has not been received is the null value.
  2. Select all product codes, unit prices, and products with a quantity ordered of 40 or greater:

    SELECT A.PCODE, PRICE, OQUANTITY FROM A
      LEFT OUTER JOIN B
      ON A.PCODE = B.PCODE AND OQUANTITY >= 40

    [Figure]

    Note
    The quantity for a product for which an order has not been received is the null value.
  3. Select all product codes, unit prices, and quantities ordered for products with a unit price of greater than or equal to 400:

    SELECT A.PCODE, PRICE, OQUANTITY FROM A
     LEFT OUTER JOIN B
     ON A.PCODE = B.PCODE AND PRICE >= 400

    [Figure]

    Note
    The quantity for a product for which an order has not been received is the null value.
  4. For each product with a unit price of 500 or less, determine its product code, unit price, orders received for the current month where the minimum size order is at least 40, and orders received for past month where the maximum size order is 30 or fewer:

    SELECT A.PCODE,A.PRICE,B.OQUANTITY,C.LMORDER
      FROM A LEFT OUTER JOIN B ON A.PCODE=B.PCODE AND
      B.OQUANTITY >= 40
            LEFT OUTER JOIN C ON A.PCODE=C.PCODE AND
              C.LMORDER=30
             WHERE A.PRICE<=500

    [Figure]

    Note
    Products with a unit price greater than 500 are not retrieved.
    NULL will be set as the ordered quantities for products that did not receive any orders, as the ordered quantity for the current month if all orders were for less than 40 (OQUANTITY), and as the ordered quantity for the past month if all orders were for more than 30 (LMORDER).
  5. For each product with a unit price of 400 or less, determine its product code, unit price, and ordered quantities for the current and past months.

    SELECT A.PCODE,A.PRICE,B.OQUANTITY,C.LMORDER
     FROM A LEFT OUTER JOIN B ON A.PCODE = B.PCODE
              AND A.PRICE <= 400
            LEFT OUTER JOIN C ON A.PCODE = C.PCODE
              AND A.PRICE <= 400

    [Figure]

    Note
    NULL will be set as the ordered quantities for products that did not receive any orders (OQUANTITY, LMORDER) and for products with a unit price greater than 400.
  6. Determine all product codes and the percentage of the orders (in quantity) received this month against the orders received last month for each product. The quantity of the product for which an order has not been received this month or last month is the null value.

    SELECT A.PCODE,100.0*B.OQUANTITY/C.LMORDER
      FROM A LEFT OUTER JOIN (B INNER JOIN C ON B.PCODE=C.PCODE)
               ON A.PCODE = B.PCODE

    [Figure]

  7. Determine sales for the current and preceding months from all product codes, unit prices, the orders received this month, and the orders received last month:

     SELECT PCODE,PRICE*OQTY,PRICE*LMORDER
       FROM(SELECT A.PCODE,A.PRICE,B.OQTY,C.LMORDER
         FROM A LEFT OUTER JOIN B ON A.PCODE = B.PCODE
                LEFT OUTER JOIN C ON A.PCODE = C.PCODE)
       AS DT1(PCODE,PRICE,OQUANTITY,LMORDER)

    [Figure]