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.
FROM table-reference [,table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [,value-expression]...]
[HAVING search-condition]
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 8 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:
A NULL, LIKE, or XLIKE predicate cannot be specified in the HAVING clause.
Set functions that can be specified in a search condition are AVG, MAX, MIN, SUM, and COUNT.
The following rules apply to the HAVING clause:
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:
Specifies grouping. A column to be specified in a GROUP BY clause is shown 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:
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:
A retrieval that specifies multiple tables or query names in a single FROM clause (a retrieval that encompasses multiple tables) is called a join.
SELECT SUM(SQUANTITY) FROM STOCK
WHERE PRICE >= (SELECT AVG(PRICE)
FROM STOCK)
Table A (outer table) lists product prices, Table B (inner table) lists the quantities of orders received, and Table C lists previous orders.
SELECT A.PCODE, PRICE, OQUANTITY FROM A
LEFT OUTER JOIN B
ON A.PCODE = B.PCODE WHERE PRICE > 200
SELECT A.PCODE, PRICE, OQUANTITY FROM A
LEFT OUTER JOIN B
ON A.PCODE = B.PCODE AND OQUANTITY >= 40
SELECT A.PCODE, PRICE, OQUANTITY FROM A
LEFT OUTER JOIN B
ON A.PCODE = B.PCODE AND PRICE >= 400
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
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
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
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)