Movatterモバイル変換

7.3.1. Select-List Items

7.3.2. Column Labels

7.3.3.DISTINCT

The simplest kind of select list is* which emits all columns that the table expression produces. Otherwise, a select list is a comma-separated list of value expressions (as defined inSection 4.2). For instance, it could be a list of column names:

SELECT a, b, c FROM ...

The columns namesa,b, andc are either the actual names of the columns of tables referenced in theFROM clause, or the aliases given to them as explained inSection 7.2.1.2. The name space available in the select list is the same as in theWHERE clause, unless grouping is used, in which case it is the same as in theHAVING clause.

If more than one table has a column of the same name, the table name must also be given, as in:

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

When working with multiple tables, it can also be useful to ask for all the columns of a particular table:

SELECT tbl1.*, tbl2.a FROM ...

SeeSection 8.16.5 for more about thetable_name.* notation.

If an arbitrary value expression is used in the select list, it conceptually adds a new virtual column to the returned table. The value expression is evaluated once for each result row, with the row's values substituted for any column references. But the expressions in the select list do not have to reference any columns in the table expression of theFROM clause; they can be constant arithmetic expressions, for instance.

The entries in the select list can be assigned names for subsequent processing, such as for use in anORDER BY clause or for display by the client application. For example:

SELECT a AS value, b + c AS sum FROM ...

If no output column name is specified usingAS, the system assigns a default column name. For simple column references, this is the name of the referenced column. For function calls, this is the name of the function. For complex expressions, the system will generate a generic name.

TheAS key word is usually optional, but in some cases where the desired column name matches aPostgreSQL key word, you must writeAS or double-quote the column name in order to avoid ambiguity. (Appendix C shows which key words requireAS to be used as a column label.) For example,FROM is one such key word, so this does not work:

SELECT a from, b + c AS sum FROM ...

but either of these do:

SELECT a AS from, b + c AS sum FROM ...SELECT a "from", b + c AS sum FROM ...

For greatest safety against possible future key word additions, it is recommended that you always either writeAS or double-quote the output column name.

After the select list has been processed, the result table can optionally be subject to the elimination of duplicate rows. TheDISTINCT key word is written directly afterSELECT to specify this:

SELECT DISTINCTselect_list ...

(Instead ofDISTINCT the key wordALL can be used to specify the default behavior of retaining all rows.)

Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.

Alternatively, an arbitrary expression can determine what rows are to be considered distinct:

SELECT DISTINCT ON (expression [,expression ...])select_list ...

Hereexpression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the“first row” of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at theDISTINCT filter. (DISTINCT ON processing occurs afterORDER BY sorting.)

TheDISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use ofGROUP BY and subqueries inFROM, this construct can be avoided, but it is often the most convenient alternative.