SelectExpression
Derby Reference Manual
91
SelectExpression
A SelectExpression is the basic SELECT-FROM-WHERE construct used to build a table
value based on filtering and projecting values from other tables.
Syntax
SELECT [ DISTINCT | ALL ] SelectItem [
, SelectItem
]*
]
]
]
SelectItem:
{
* |
{
Expression [AS
}
The SELECT clause contains a list of expressions and an optional quantifier that is
applied to the results of the
specified, only one copy of any row value is included in the result. Nulls are considered
duplicates of one another for the purposes of DISTINCT. If no quantifier, or ALL, is
specified, no rows are removed from the result in applying the SELECT clause (ALL is
the default).
A SelectItem projects one or more result column values for a table result being
constructed in a SelectExpression.
For queries that do not select a specific column from the tables involved in the
SelectExpression (for example, queries that use
COUNT(*)
), the user must have at
least one column-level SELECT privilege or table-level SELECT privilege. See
The result of the
is the cross product of the FROM items. The
can further qualify this result.
The WHERE clause causes rows to be filtered from the result based on a boolean
expression. Only rows for which the expression evaluates to TRUE are returned in the
result.
The GROUP BY clause groups rows in the result into subsets that have matching values
for one or more columns. GROUP BY clauses are typically used with aggregates.
If there is a GROUP BY clause, the SELECT clause must contain only aggregates or
grouping columns. If you want to include a non-grouped column in the SELECT clause,
include the column in an aggregate expression. For example:
-- List head count of each department,
-- the department number (WORKDEPT), and the average departmental salary
-- (SALARY) for all departments in the EMPLOYEE table.
-- Arrange the result table in ascending order by average departmental
-- salary.
SELECT COUNT(*),WORK_DEPT,AVG(SALARY)
FROM EMPLOYEE
GROUP BY WORK_DEPT
ORDER BY 3
