background image
<< The result of a SelectExpression | NEXT VALUE FOR expression >>

TableExpression

<< The result of a SelectExpression | NEXT VALUE FOR expression >>
Derby Reference Manual
93
FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks
WHERE t.TABLENAME = 'FLIGHTS'
AND t.TABLEID = col.REFERENCEID
AND t.TABLEID = cons.TABLEID
AND cons.CONSTRAINTID = checks.CONSTRAINTID
ORDER BY CONSTRAINTNAME
-- This example shows the use of the DISTINCT clause
SELECT DISTINCT ACTNO
FROM EMP_ACT
-- This example shows how to rename an expression
-- Using the EMPLOYEE table, list the department number (WORKDEPT) and
-- maximum departmental salary (SALARY) renamed as BOSS
-- for all departments whose maximum salary is less than the
-- average salary in all other departments.
SELECT WORKDEPT AS DPT, MAX(SALARY) AS BOSS
FROM EMPLOYEE EMP_COR
GROUP BY WORKDEPT
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)
ORDER BY BOSS
TableExpression
A TableExpression specifies a table, view, or function in a
FROM clause
. It is the source
from which a
SelectExpression
selects a result.
A correlation name can be applied to a table in a TableExpression so that its columns
can be qualified with that name. If you do not supply a correlation name, the table name
qualifies the column name. When you give a table a correlation name, you cannot use
the table name to qualify columns. You must use the correlation name when qualifying
column names.
No two items in the FROM clause can have the same correlation name, and no
correlation name can be the same as an unqualified table name specified in that FROM
clause.
In addition, you can give the columns of the table new names in the AS clause. Some
situations in which this is useful:
· When a
VALUES expression
is used as a
TableSubquery
, since there is no other
way to name the columns of a
VALUES expression
.
· When column names would otherwise be the same as those of columns in other
tables; renaming them means you don't have to qualify them.
The Query in a
TableSubquery
appearing in a FromItem can contain multiple columns
and return multiple rows. See
TableSubquery
.
For information about the optimizer overrides you can specify, see Tuning Derby.
Syntax
{
TableViewOrFunctionExpression
|
JOIN operation
}
Example
-- SELECT from a JOIN expression
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E LEFT OUTER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO