Table Expression (table_expression)

A table expression (table_expression) specifies a simple or a grouped result table.

Structure

Syntax Syntax

<table_expression> ::=
  <from_clause> [<where_clause>] [<group_clause>] [<having_clause>]

<from_clause> ::=
  FROM <from_table_spec>,...

<where_clause> ::=
  WHERE <search_condition>

<group_clause> ::=
  GROUP BY <expression>,...

<having_clause> ::=
  HAVING <search_condition>

<from_table_spec> ::=
  <table_name> [AS] [<reference_name>]
| <result_table_name> [AS] [<reference_name>]
| (<query_expression>) [AS] [<reference_name>]
| <joined_table>
| (<from_table_spec>,...)

End of the code.

Examples

SQL Tutorial, Selecting and Arranging Rows, Creating Groups: GROUP BY, HAVING

Explanation

A table expression produces a temporary result table. If there are no optional clauses, this temporary result table is the result of the FROM clause. Otherwise, each specified clause is applied to the result of the previous condition and the table is the result of the last specified clause. The temporary result table contains all of the columns in all the tables listed in the FROM clause.

The order of the GROUP and HAVING clauses is random.

FROM Clause (from_clause)

The FROM clause from_clause specifies a table. This table can be derived from several base, view, and result tables. The number of underlying tables in a FROM clause is equal to the total number of underlying tables in each FROM TABLE specification from_table_spec. The number of underlying tables in a FROM clause must not exceed 64.

The database user must have the SELECT privilege for each specified table or for at least one column in the specified table.

The result of a FROM clause is a table that is generated from the specified tables as follows:

If the FROM clause comprises a single FROM TABLE specification, the result is the specified table.
If the FROM clause contains more than one FROM TABLE specification, a result table is built that includes all possible combinations of all rows of the first table with all rows of the second table, etc. From a mathematical perspective, this is the Cartesian product of all the tables.

This rule describes the effect of the FROM clause, not its actual implementation.

WHERE Clause (where_clause)

The WHERE clause where_clause specifies the conditions for building a result table.

The search condition search_condition is applied to each row in the temporary result table formed by the FROM clause. The result of the WHERE clause is a table that only contains those rows from the result table for which the search condition is true.

The search condition may only contain column specifications for which the user has the SELECT privilege.

Each column specification directly contained in the search condition must uniquely identify a column from the tables specified in the FROM clause of the table expression. If necessary, the column name must be qualified with the table identifier. If reference names reference_name are defined in the FROM clause for table names, they must be used as table identifiers in the search condition.

Expressions expression in the search condition must not contain a set function, except in the exception below:

Example Example

SELECT ... FROM uppertab,... HAVING ...

(SELECT ... WHERE MIN(uppertab, ...)... )

The SELECT statement is allowed in the specified format.

End of the example.

In a subquery used in a HAVING clause, it is possible to use WHERE clauses that contain set functions for the columns of the table specified in the SELECT ... HAVING statement.

In the case of correlated subqueries, a column specification can identify a column in a table that was specified in a FROM clause of a different table expression in the QUERY specification.

Each subquery in the search condition is usually evaluated only once. In the case of a correlated subquery, the subquery is executed for each row in the result table generated by the FROM clause.

GROUP Clause (group_clause)

The GROUP clause group_clause specifies grouping criteria for a result table.

Each column name specified in the GROUP clause must identify a result_column_name in the selected columns of the QUERY specification or uniquely identify a column in the tables on which the QUERY specification is based. If necessary, the column name must be qualified with the table identifier.

The GROUP clause allows the set functions AVG, COUNT, MAX/MIN, STDDEV, SUM, and VARIANCE to be applied not only to the entire result table, but also to groups of rows within a result table. A group is defined by the grouping columns specified in GROUP BY. All rows of a group have the same values in the grouping columns. Rows containing the NULL value in a grouping column are combined to form a group. The same is true for the special NULL value.

GROUP BY generates one row for each group in the result table. The selected columns in the QUERY specification, therefore, may contain only those grouping columns, operations on the grouping columns, and expressions that use the functions SUM, AVG, MAX/MIN, COUNT, STDDEV, and VARIANCE.

If no rows satisfy the conditions indicated in the WHERE clause and a GROUP clause was specified, the result table is empty.

Specifying scalar subqueries is not permissible in a GROUP clause.

HAVING Clause (having_clause)

The HAVING clause having_clause specifies the properties of a group.

Each expression in the search condition search_condition that does not occur in the argument of a set function must identify a grouping column.

If the HAVING clause is used without a GROUP clause, the result table built so far is regarded as a group.

The search condition is applied to each group in the result table. The result of the HAVING clause is a table that only contains those groups for which the search condition is true.

FROM TABLE Specification (from_table_spec)

Each FROM TABLE specification from_table_spec in a FROM clause specifies either no table identifier, one table identifier, or any number of table identifiers.

Reference Name (reference_name)

If a FROM TABLE specification does not contain a reference name, the table name table_name or result table name result_table_name is the table identifier. If a FROM TABLE specification contains a reference name, the reference name is the table identifier.

Each reference name must be different from each identifier that specifies a table name. If a result table name is a table identifier, there must not be any table identifiers table_name in the form [<schema_name>.]<result_table_name>. Each table identifier must differ from every other table identifier.

The validity range of the table identifiers is the entire QUERY specification within which the FROM TABLE specification is used. If column names are to be qualified within the QUERY specification, table identifiers must be used for this purpose.

Reference names are essential for formulating JOIN conditions within a table. For example, FROM HOTEL, HOTEL X defines a reference name X for the second occurrence of the HOTEL table. Reference names are also necessary sometimes to formulate correlated subqueries. Similarly, a reference name is required if a column in the result of a QUERY expression can only be identified uniquely by specifying the reference name.

Number of Underlying Tables

If a FROM TABLE specification denotes a base table, result table, or the result of a QUERY expression, the number of tables underlying this FROM TABLE specification is equal to 1.

If a FROM TABLE specification denotes a complex view table, the number of tables underlying this FROM TABLE specification is equal to 1.

If a FROM TABLE specification denotes a view table that is not a complex view table, the number of underlying tables is equal to the number of tables underlying the FROM clause of the view table.

If a FROM TABLE specification denotes a joined table joined_table, the number of tables underlying this FROM TABLE specification is equal to the total number of underlying tables of the FROM TABLE specifications contained in it.

QUERY Expression (query_expression)

A FROM TABLE specification that contains a QUERY expression query_expression specifies a table identifier only if a reference name reference_name is specified.

If a FROM TABLE specification contains a QUERY expression, a result table is built that matches this QUERY expression. This result table obtains a system-internal name that collides neither with an unnamed nor a named result table. While the FROM condition is being processed, the result of the QUERY expression is used in the same way as a named result table and is deleted implicitly after processing.

A table expression in which at least an OUTER JOIN indicator or OUTER JOIN TYPE (LEFT | RIGHT | FULL) is specified (see JOIN predicate for more on this) is subject to strict restrictions when it is to be based on more than two tables. For this reason, a QUERY expression is frequently required to formulate a QUERY specification that is to be based on at least three tables and in which at least one OUTER JOIN indicator is used in a JOIN predicate.

JOINED TABLE (joined_table)

A FROM TABLE specification containing a joined table joined_table specifies the number of table identifiers that are specified by the FROM TABLE specifications it contains.

(<from_table_spec>,...)

You can use round brackets to control the evaluation of table information, especially if several tables are being used in the table expression. The transformation of bracketed information to prepare the evaluation only takes place when automatic transformation of SQL statements is activated (Special Database Parameters EnableQuery=EXTENDED and EnableQueryRewrite=YES).

Example Example

The SQL statement

SELECT * FROM (t1,t2),(t3,t4)

is transformed as follows:

SELECT * FROM (SELECT * FROM t1,t2),(SELECT * FROM t3,t4)

End of the example.

More Information

Restrictions for SQL Statements

Privileges: Overview