- ALL | DISTINCT
The DISTINCT keyword indicates that duplicate values found in two or more rows will not be shown after the first row. The ALL keyword explicitly reinforces the default to retrieve all rows regardless of uniqueness.
Note that the ORDER BY clause sorts rows before the DISTINCT clause removes non-unique rows. Use these clauses together to ensure that the row found is the row you intend to retrieve.
- DISTINCT ON
The ON keyword, following the DISTINCT keyword, allows you to specify one or more distinct_expressions by which to judge uniqueness.
- distinct_expression
A column name within a from_item, or a valid expression, whose value is used by the DISTINCT ON clause as a basis for removing duplicate values.
- target_expression
A column name within a from_item, or a valid expression.
- output_name
An alternate name for an output column, following the AS clause. This name will then be used during display of the output and can be used to reference the column within ORDER BY and GROUP BY clauses in the same SELECT statement. However, this name does not apply to the WHERE or HAVING clauses; you will need to use the correct column name for them.
- FROM
The clause which is passed from_items, from which to retrieve rows.
- from_item
The name of a table, a subselect, or a JOINed set of from_items that you wish to retrieve data from.
- { , | CROSS JOIN }
The comma (or formal CROSS JOIN clause) separates multiple from_items.
- WHERE
The clause that is passed conditions by which to constraint a result set.
- condition
An expression that yields either true or false, applied conditionally to non-grouped target expressions.
- GROUP BY
The clause that is passed aggregate_expressions to aggregate (group) rows together.
- aggregate_expression
A column name within a from_item, or a valid expression, to be used as a basis to aggregate (group) rows together.
- HAVING
The clause to which is passed any aggregate_conditions by which to constrain a result set.
- aggregate_condition
An expression that yields either true or false, applied conditionally to aggregated (grouped) target expressions.
- UNION
The clause that combines two result sets with compatible column structure into a single combined result set.
- INTERSECT
The clause that removes any rows from the initial result set not found in the following select statement's result set (resulting in the overlapping, or intersecting, set).
- EXCEPT
The clause that removes any rows from the initial result set that are found in the following select statement's result set (resulting in the difference set).
- select
A full select statement. The limitation on this form of subquery is that you cannot use any of the ORDER BY, FOR UPDATE, or LIMIT clauses unless the statement is enclosed in parentheses.
- ORDER BY
The ORDER BY clause sorts the retrieved result set by each order_expression provided.
- order_expression [ ASC | DESC | USING operator]
A column name in the retrieved result set by which the ORDER BY clause sorts the results. The use of the ASC keyword explicitly defines the default of ascending sorting, while the DESC implies descending sorting. The USING clause defines an operator (e.g., >) to compare subsequent order_expression values with.
- FOR UPDATE
The locking clause that places an implicit ROW SHARE MODE lock (see LOCK") on the from_item table selected in the current transaction.
- OF update_table
A specific table to which to apply ROW SHARE MODE locking when multiple tables are selected in the FROM clause.
- LIMIT
The LIMIT clause constrains only a specified portion of the retrieved results.
- ALL | count
The ALL keyword explicitly specifies the default, which is to not limit the number of rows returned. The use of a numeric count value limits the number of rows in the retrieved result set to count.
- { OFFSET | , } start
The OFFSET keyword (or informal comma, following the LIMIT clause) allows a result set to ignore the first start rows.