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 ]*
FROM clause
[ WHERE clause ]
[ GROUP BY clause ]
[ HAVING clause ]

SelectItem:

{
    * |
    { table-Name | correlation-Name } .* |
    Expression [AS Simple-column-Name ]
}

The SELECT clause contains a list of expressions and an optional quantifier that is applied to the results of the FROM clause and the WHERE clause. If DISTINCT is 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.

The result of the FROM clause is the cross product of the FROM items. The WHERE clause 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 WORKDEPT, AVG(SALARY)
     FROM EMPLOYEE 
     GROUP BY WORKDEPT 
     ORDER BY 3;

If there is no GROUP BY clause, but a SelectItem contains an aggregate not in a subquery, the query is implicitly grouped. The entire table is the single group.

The HAVING clause restricts a grouped table, specifying a search condition (much like a WHERE clause) that can refer only to grouping columns or aggregates from the current scope. The HAVING clause is applied to each group of the grouped table. If the HAVING clause evaluates to TRUE, the row is retained for further processing. If the HAVING clause evaluates to FALSE or NULL, the row is discarded. If there is a HAVING clause but no GROUP BY, the table is implicitly grouped into one group for the entire table.

Derby processes a SelectExpression in the following order:

• FROM clause
• WHERE clause
• GROUP BY (or implicit GROUP BY)
• HAVING clause
• SELECT clause

The result of a SelectExpression is always a table.

When a query does not have a FROM clause (when you are constructing a value, not getting data out of a table), you use a VALUES statement, not a SelectExpression. For example:

VALUES CURRENT_TIMESTAMP

See VALUES expression.

The * Wildcard

* expands to all columns in the tables in the associated FROM clause.

table-Name.* and correlation-Name.* expand to all columns in the identified table. That table must be listed in the associated FROM clause.

Naming Columns

You can name a SelectItem column using the AS clause. When the SelectExpression appears in a UNION, the names from the first SelectExpression are taken as the names for the columns in the result of the UNION. If a column of a SelectItem is not a simple ColumnReference expression or named with an AS clause, it is given a generated unique name.

These column names are useful in several cases:

• They are made available on the JDBC ResultSetMetaData.
• They are used as the names of the columns in the resulting table when the SelectExpression is used as a table subquery in a FROM clause.
• They are used in the ORDER BY clause as the column names available for sorting.

Examples

-- this example shows SELECT-FROM-WHERE
-- with an ORDER BY clause
-- and correlation-Names for the tables 
SELECT CONSTRAINTNAME, COLUMNNAME 
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;