A SelectExpression is the basic SELECT-FROM-WHERE construct used to build a table value based on filtering and projecting values from other tables.
SelectItem:
The SELECT clause contains a list of expressions and
an optional quantifier that is applied to the results of the
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
The
result of the
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:
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.
The ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function.
The result offset clause provides a way to skip the N first rows in a result set before starting to return any rows. The fetch first clause, which can be combined with the result offset clause if desired, limits the number of rows returned in the result set.
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 expression, not a SelectExpression.
For example:
See
* expands to all columns in the tables in the associated FROM clause.
You can name a SelectItem column using the AS clause. 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: