SELECT(l)                                               SELECT(l)

NAME
       SELECT - Retrieve rows from a table or view.

SYNOPSIS
       SELECT [ ALL | DISTINCT [ ON column ] ]
           expression [ AS name ] [, ...]
           [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
           [ FROM table [ alias ] [, ...] ]
           [ WHERE condition ]
           [ GROUP BY column [, ...] ]
           [ HAVING condition [, ...] ]
           [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
           [ ORDER BY column [ ASC | DESC ] [, ...] ]
           [ FOR UPDATE [ OF class_name... ] ]
           [ LIMIT { count | ALL } [ { OFFSET | , } count ] ]

   INPUTS
       expression
              The name of a table's column or an expression.

       name   Specifies  another  name for a column or an expres-
              sion using the AS clause. name cannot  be  used  in
              the WHERE condition. It can, however, be referenced
              in associated ORDER BY or GROUP BY clauses.

       TEMPORARY

       TEMP   The table is created unique to this session, and is
              automatically dropped on session exit.

       new_table
              If  the  INTO TABLE clause is specified, the result
              of the query will be stored in another  table  with
              the  indicated  name.  The target table (new_table)
              will be created automatically and should not  exist
              before this command.  Refer to SELECT INTO for more
              information.

              Note: The CREATE TABLE AS statement will also  cre-
              ate a new table from a select query.

       table  The  name  of  an  existing table referenced by the
              FROM clause.

       alias  An alternate name for the preceding table.   It  is
              used  for  brevity  or  to  eliminate ambiguity for
              joins within a single table.

       condition
              A boolean expression giving a  result  of  true  or
              false.  See the WHERE clause.

       column The name of a table's column.

       select A  select  statement  with  all features except the
              ORDER BY clause.

   OUTPUTS
       Rows   The complete set of rows resulting from  the  query
              specification.

       count  The count of rows returned by the query.

DESCRIPTION
       SELECT  will  return rows from one or more tables.  Candi-
       dates for selection are rows which satisfy the WHERE  con-
       dition;  if  WHERE  is  omitted,  all rows are candidates.
       (See select(l).)

       DISTINCT will eliminate all duplicate rows from the selec-
       tion.  DISTINCT ON column will eliminate all duplicates in
       the specified column; this is equivalent to using GROUP BY
       column.   ALL  will  return  all candidate rows, including
       duplicates.

       The GROUP BY clause allows a user to divide a  table  con-
       ceptually into groups.  (See select(l).)

       The HAVING clause specifies a grouped table derived by the
       elimination of groups from the result  of  the  previously
       specified clause.  (See select(l).)

       The  ORDER  BY clause allows a user to specify that he/she
       wishes the rows  sorted  according  to  the  ASCending  or
       DESCending mode operator.  (See select(l).)

       The UNION clause allows the result to be the collection of
       rows returned by the queries involved.  (See select(l).)

       The INTERSECT give you the rows that are  common  to  both
       queries.  (See select(l).)

       The EXCEPT give you the rows in the upper query not in the
       lower query.  (See select(l).)

       The FOR UPDATE clause allows the SELECT statement to  per-
       form exclusive locking of selected rows.

       The  LIMIT  clause  allows  control  over  which  rows are
       returned by the query.

       You must have SELECT privilege to a table to read its val-
       ues (See the GRANT/REVOKE statements).

   WHERE CLAUSE
       The optional WHERE condition has the general form:

       WHERE boolean_expr

       boolean_expr can consist of any expression which evaluates
       to a boolean value.  In many cases, this  expression  will
       be

            expr cond_op expr

       or

            log_op expr

       where cond_op can be one of: =, <, <=, >, >= or <>, a con-
       ditional operator like ALL, ANY, IN, LIKE, or  a  locally-
       defined  operator, and log_op can be one of: AND, OR, NOT.
       The comparison  returns  either  TRUE  or  FALSE  and  all
       instances will be discarded if the expression evaluates to
       FALSE.

   GROUP BY CLAUSE
       GROUP BY specifies a grouped table derived by the applica-
       tion of this clause:

       GROUP BY column [, ...]

       GROUP  BY  will  condense  into a single row all rows that
       share the same values for the grouped columns;  aggregates
       return  values  derived  from  all  rows  that make up the
       group. The value returned for an  ungrouped  and  unaggre-
       gated  column is dependent on the order in which rows hap-
       pen to be read from the database.

   HAVING CLAUSE
       The optional HAVING condition has the general form:

       HAVING cond_expr

       where cond_expr is the same as  specified  for  the  WHERE
       clause.

       HAVING  specifies  a grouped table derived by the elimina-
       tion of groups from the result of the previously specified
       clause that do not meet the cond_expr.

       Each  column  referenced  in cond_expr shall unambiguously
       reference a grouping column.

   ORDER BY CLAUSE
       ORDER BY column [ ASC | DESC ] [, ...]

       column can be either a column name or an ordinal number.

       The ordinal numbers refers to the ordinal  (left-to-right)
       position  of the column. This feature makes it possible to
       define an ordering on the basis of a column that does  not
       have  a  proper  name.  This is never absolutely necessary
       because it is always possible assign a name  to  a  calcu-
       lated column using the AS clause, e.g.:

       SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;

       From  release  6.4 of PostgreSQL, the columns in the ORDER
       BY clause do not need to  appear  in  the  SELECT  clause.
       Thus the following statement is now legal:

       SELECT name FROM distributors ORDER BY code;

       Optionally  one  may  add the keyword DESC (descending) or
       ASC (ascending) after each column name  in  the  ORDER  BY
       clause.  If not specified, ASC is assumed by default.

   UNION CLAUSE
       table_query UNION [ ALL ] table_query
           [ ORDER BY column [ ASC | DESC ] [, ...] ]

       where  table_query specifies any select expression without
       an ORDER BY clause.

       The UNION clause allows the result to be the collection of
       rows returned by the queries involved. (See UNION clause).
       The two tables that represent the direct operands  of  the
       UNION  must  have  the  same number of columns, and corre-
       sponding columns must be of compatible data types.

       By default, the result  of  UNION  does  not  contain  any
       duplicate rows unless the ALL clause is specified.

       Multiple  UNION operators in the same SELECT statement are
       evaluated left to right.  Note that the ALL keyword is not
       global  in nature, being applied only for the current pair
       of table results.

   INTERSECT CLAUSE
       table_query INTERSECT table_query
           [ ORDER BY column [ ASC | DESC ] [, ...] ]

       where table_query specifies any select expression  without
       an ORDER BY clause.

       The INTERSECT clause allows the result to be all rows that
       are common to the involved queries.  The two  tables  that
       represent  the  direct operands of the INTERSECT must have
       the same number of columns, and corresponding columns must
       be of compatible data types.

       Multiple  INTERSECT operators in the same SELECT statement
       are evaluated left to right.

   EXCEPT CLAUSE
       table_query EXCEPT table_query
            [ ORDER BY column [ ASC | DESC ] [, ...] ]

       where table_query specifies any select expression  without
       an ORDER BY clause.

       The  EXCEPT  clause  allows the result to be rows from the
       upper query that are not in the lower query.  (See  EXCEPT
       clause).    The  two  tables  that  represent  the  direct
       operands of the  EXCEPT  must  have  the  same  number  of
       columns,  and  corresponding columns must be of compatible
       data types.

       Multiple EXCEPT operators in the same SELECT statement are
       evaluated left to right.

USAGE
       To join the table films with the table distributors:

       SELECT f.title, f.did, d.name, f.date_prod, f.kind
           FROM distributors d, films f
           WHERE f.did = d.did

       title                    |did|name            | date_prod|kind
       -------------------------+---+----------------+----------+----------
       The Third Man            |101|British Lion    |1949-12-23|Drama
       The African Queen        |101|British Lion    |1951-08-11|Romantic
       Une Femme est une Femme  |102|Jean Luc Godard |1961-03-12|Romantic
       Vertigo                  |103|Paramount       |1958-11-14|Action
       Becket                   |103|Paramount       |1964-02-03|Drama
       48 Hrs                   |103|Paramount       |1982-10-22|Action
       War and Peace            |104|Mosfilm         |1967-02-12|Drama
       West Side Story          |105|United Artists  |1961-01-03|Musical
       Bananas                  |105|United Artists  |1971-07-13|Comedy
       Yojimbo                  |106|Toho            |1961-06-16|Drama
       There's a Girl in my Soup|107|Columbia        |1970-06-11|Comedy
       Taxi Driver              |107|Columbia        |1975-05-15|Action
       Absence of Malice        |107|Columbia        |1981-11-15|Action
       Storia di una donna      |108|Westward        |1970-08-15|Romantic
       The King and I           |109|20th Century Fox|1956-08-11|Musical
       Das Boot                 |110|Bavaria Atelier |1981-11-11|Drama
       Bed Knobs and Broomsticks|111|Walt Disney     |          |Musical

       To  sum  the column len of all films and group the results
       by kind:

       SELECT kind, SUM(len) AS total FROM films GROUP BY kind;

           kind      |total
           ----------+------
           Action    | 07:34
           Comedy    | 02:58
           Drama     | 14:28
           Musical   | 06:42
           Romantic  | 04:38

       To sum the column len of all films, group the  results  by
       kind  and  show  those  group  totals that are less than 5
       hours:

       SELECT kind, SUM(len) AS total
           FROM films
           GROUP BY kind
           HAVING SUM(len) < INTERVAL '5 hour';

           kind      |total
           ----------+------
           Comedy    | 02:58
           Romantic  | 04:38

       The following two examples are identical ways  of  sorting
       the  individual  results  according to the contents of the
       second column (name):

       SELECT * FROM distributors ORDER BY name;
       SELECT * FROM distributors ORDER BY 2;

           did|name
           ---+----------------
           109|20th Century Fox
           110|Bavaria Atelier
           101|British Lion
           107|Columbia
           102|Jean Luc Godard
           113|Luso films
           104|Mosfilm
           103|Paramount
           106|Toho
           105|United Artists
           111|Walt Disney
           112|Warner Bros.
           108|Westward

       This example shows how to obtain the union of  the  tables
       distributors  and actors, restricting the results to those
       that begin with letter W in each table. Only distinct rows
       are to be used, so the ALL keyword is omitted:

           --        distributors:                actors:
           --        did|name                     id|name
           --        ---+------------             --+--------------
           --        108|Westward                  1|Woody Allen
           --        111|Walt Disney               2|Warren Beatty
           --        112|Warner Bros.              3|Walter Matthau
           --        ...                           ...

       SELECT distributors.name
           FROM   distributors
           WHERE  distributors.name LIKE 'W%'
       UNION
       SELECT actors.name
           FROM   actors
           WHERE  actors.name LIKE 'W%'

       name
       --------------
       Walt Disney
       Walter Matthau
       Warner Bros.
       Warren Beatty
       Westward
       Woody Allen

COMPATIBILITY
   EXTENSIONS
       Postgres  allows one to omit the FROM clause from a query.
       This feature was retained from the original PostQuel query
       language:

       SELECT distributors.* WHERE name = 'Westwood';

           did|name
           ---+----------------
           108|Westward

   SQL92
   SELECT CLAUSE
       In  the  SQL92 standard, the optional keyword "AS" is just
       noise and can be omitted without  affecting  the  meaning.
       The  Postgres  parser  requires this keyword when renaming
       columns because the type extensibility  features  lead  to
       parsing ambiguities in this context.

       In the SQL92 standard, the new column name specified in an
       "AS" clause may be  referenced  in  GROUP  BY  and  HAVING
       clauses.  This is not currently allowed in Postgres.

       The DISTINCT ON phrase is not part of SQL92.

   UNION CLAUSE
       The  SQL92  syntax  for  UNION allows an additional CORRE-
       SPONDING BY clause:

       table_query UNION [ALL]
           [CORRESPONDING [BY (column [,...])]]
           table_query

       The CORRESPONDING BY clause is not supported by  Postgres.

SQL - Language Statements 15 August 1999                        1