7.4.1 Specification format and rules for table expressions

(1) Specification format

table-expression ::= FROM-clause
                    [WHERE-clause]
                    [GROUP-BY-clause]
                    [HAVING-clause]

To Page Top

(2) Explanation of specification format

FROM-clause:

The FROM clause specifies the tables from which results are to be retrieved. For details about the FROM clause, see 7.5 FROM clause.

WHERE-clause:

The WHERE clause specifies the search conditions. For details about the WHERE clause, see 7.6 WHERE clause.

GROUP-BY-clause:

Specify the GROUP BY clause when you want to aggregate the retrieval data into groups. For details about the GROUP BY clause, see 7.7 GROUP BY clause.

HAVING-clause:

The HAVING clause specifies criteria for the data aggregated of the groups created by the GROUP BY clause. For details about the HAVING clause, see 7.8 HAVING clause.

To Page Top

(3) Rules

1. Any column in the results of a table expression can be referenced as a column specification.

2. If there is no WHERE clause, GROUP BY clause, or HAVING clause, the result of the table expression will be determined using only the FROM clause. Otherwise, the results of each clause you specify are applied to the clause specified immediately after it. The result of the table expression will be the result of the last specified clause.

   For example, consider what happens when the SELECT statement shown below is executed.

   Example:

   Using the data in the sales history table (SALESLIST), this example determines the sum of the quantities purchased on or after September 3, 2011 by product code (PUR-CODE). Furthermore, it retrieves only the product codes for which the sum of the quantities purchased is 20 or fewer.

   SELECT "PUR-CODE",SUM("PUR-NUM")
       FROM "SALESLIST"
           WHERE "PUR-DATE">=DATE'2011-09-03'
           GROUP BY "PUR-CODE"
           HAVING SUM("PUR-NUM")<=20

   Explanation

   The underlined portion indicates the table expression. When the SELECT statement above is executed, the result of the table expression is determined by the following steps.

   1. The result of the FROM clause is applied to the WHERE clause. In this step, data from the SALESLIST table where the PUR-DATE column is September 3, 2011 or later is extracted (data from both the PUR-CODE and PUR-NUM columns).

   2. The results extracted in step 1 are grouped using the GROUP BY clause. In this case, the results are aggregated by PUR-CODE.

   3. The results aggregated in step 2 are filtered using the HAVING clause. In this case, only data where the sum of the PUR-NUM column values does not exceed 20 are selected. This final set of data becomes the result of the table expression.

To Page Top

(4) Example

An example of a table expression is given below.

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 table expression.

To Page Top