SELECT - Retrieves rows from a table or view


SYNOPSIS

       SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
           * | expression [ AS output_name ] [, ...]
           [ FROM from_item [, ...] ]
           [ WHERE condition ]
           [ GROUP BY expression [, ...] ]
           [ HAVING condition [, ...] ]
           [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
           [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
           [ FOR UPDATE [ OF tablename [, ...] ] ]
           [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

       where from_item can be:

       [ ONLY ] table_name [ * ]
           [ [ AS ] alias [ ( column_alias_list ) ] ]
       |
       ( select )
           [ AS ] alias [ ( column_alias_list ) ]
       |
       from_item [ NATURAL ] join_type from_item
           [ ON join_condition | USING ( join_column_list ) ]

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

       output_name
              Specifies  another  name for an output column using
              the AS clause. This name is primarily used to label
              the  column  for  display.  It  can also be used to
              refer to the column's value in ORDER BY  and  GROUP
              BY  clauses.  But the output_name cannot be used in
              the WHERE or HAVING clauses; write out the  expres­
              sion instead.

       from_item
              A  table reference, sub-SELECT, or JOIN clause. See
              below for details.

       condition
              A boolean expression giving a  result  of  true  or
              false.   See  the  WHERE and HAVING clause descrip­
              tions below.

       select A select statement with  all  features  except  the
              ORDER BY, FOR UPDATE, and LIMIT clauses (even those
              can be used when the select is parenthesized).


       table_name
              The name of an existing table or view. If  ONLY  is
              specified,  only  that table is scanned. If ONLY is
              not specified, the table  and  all  its  descendant
              tables  (if  any) are scanned. * can be appended to
              the table name to indicate that  descendant  tables
              are  to  be scanned, but as of Postgres 7.1 this is
              the default behavior. (In releases before 7.1, ONLY
              was the default behavior.)

       alias  A substitute name for the preceding table_name.  An
              alias is used for brevity or to eliminate ambiguity
              for  self-joins  (where  the  same table is scanned
              multiple times). If an alias is written,  a  column
              alias  list  can also be written to provide substi­
              tute names for one or more columns of the table.

       select A sub-SELECT can appear in the  FROM  clause.  This
              acts  as though its output were created as a tempo­
              rary table for the duration of this  single  SELECT
              command.  Note  that  the  sub-SELECT  must be sur­
              rounded by parentheses, and an alias must  be  pro­
              vided for it.

       join_type
              One of [ INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [
              OUTER ] JOIN, FULL [ OUTER ] JOIN, or  CROSS  JOIN.
              For INNER and OUTER join types, exactly one of NAT­
              URAL,  ON  join_condition,  or  USING  (  join_col­
              umn_list  )  must  appear.  For CROSS JOIN, none of
              these items may appear.

       join_condition
              A qualification condition. This is similar  to  the
              WHERE  condition except that it only applies to the
              two from_items being joined in this JOIN clause.

       join_column_list
              A USING column list ( a, b, ... ) is shorthand  for
              the  ON  condition left_table.a = right_table.a AND
              left_table.b = right_table.b ...

   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.

       condition;  if  WHERE is omitted, all rows are candidates.
       (See WHERE Clause [select(l)].)

       Actually, the returned rows are not directly the rows pro­
       duced  by  the FROM/WHERE/GROUP BY/HAVING clauses; rather,
       the output rows are formed by computing the SELECT  output
       expressions  for  each  selected row.  * can be written in
       the output list as a shorthand for all the columns of  the
       selected  rows.  Also,  one  can  write table_name.*  as a
       shorthand for the columns coming from just that table.

       DISTINCT will eliminate duplicate rows  from  the  result.
       ALL  (the default) will return all candidate rows, includ­
       ing duplicates.

       DISTINCT ON eliminates rows that match on all  the  speci­
       fied  expressions,  keeping only the first row of each set
       of duplicates. The DISTINCT ON expressions are interpreted
       using  the  same  rules  as for ORDER BY items; see below.
       Note that "the first row" of  each  set  is  unpredictable
       unless  ORDER  BY  is  used to ensure that the desired row
       appears first. For example,

               SELECT DISTINCT ON (location) location, time, report
               FROM weatherReports
               ORDER BY location, time DESC;

       retrieves the most recent weather report  for  each  loca­
       tion.  But if we had not used ORDER BY to force descending
       order of time values for each location, we'd have gotten a
       report of unpredictable age for each location.

       The  GROUP  BY clause allows a user to divide a table into
       groups of rows that match on one  or  more  values.   (See
       GROUP BY Clause [select(l)].)

       The HAVING clause allows selection of only those groups of
       rows meeting the specified condition.  (See HAVING  Clause
       [select(l)].)

       The  ORDER BY clause causes the returned rows to be sorted
       in a specified order. If ORDER BY is not given,  the  rows
       are  returned  in whatever order the system finds cheapest
       to produce.  (See ORDER BY Clause [select(l)].)

       SELECT queries can be combined using UNION, INTERSECT, and
       EXCEPT  operators.  Use parentheses if necessary to deter­
       mine the ordering of these operators.

       The  UNION  operator  computes  the  collection  of   rows
       returned  by  the  queries  involved.   Duplicate rows are
       eliminated unless ALL is  specified.   (See  UNION  Clause


       The  INTERSECT  operator computes the rows that are common
       to both queries.  Duplicate rows are eliminated unless ALL
       is specified.  (See INTERSECT Clause [select(l)].)

       The  EXCEPT  operator  computes  the  rows returned by the
       first query but not the second query.  Duplicate rows  are
       eliminated  unless  ALL  is specified.  (See EXCEPT Clause
       [select(l)].)

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

       The  LIMIT  clause allows a subset of the rows produced by
       the query to be returned to the user.  (See  LIMIT  Clause
       [select(l)].)

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

   FROM CLAUSE
       The FROM clause specifies one or more  source  tables  for
       the SELECT.  If multiple sources are specified, the result
       is conceptually the Cartesian product of all the  rows  in
       all  the  sources --- but usually qualification conditions
       are added to restrict the returned rows to a small  subset
       of the Cartesian product.

       When  a  FROM  item  is a simple table name, it implicitly
       includes rows from sub-tables  (inheritance  children)  of
       the table.  ONLY will suppress rows from sub-tables of the
       table. Before Postgres 7.1, this was the  default  result,
       and adding sub-tables was done by appending * to the table
       name.  This old behaviour is available via the command SET
       SQL_Inheritance TO OFF;

       A  FROM  item can also be a parenthesized sub-SELECT (note
       that an alias clause is required for a sub-SELECT!).  This
       is  an  extremely handy feature since it's the only way to
       get multiple levels of grouping, aggregation,  or  sorting
       in a single query.

       Finally,  a FROM item can be a JOIN clause, which combines
       two simpler FROM items. (Use parentheses if  necessary  to
       determine the order of nesting.)

       A  CROSS JOIN or INNER JOIN is a simple Cartesian product,
       the same as you get from listing the two items at the  top
       level  of FROM.  CROSS JOIN is equivalent to INNER JOIN ON
       (TRUE), that is, no rows  are  removed  by  qualification.
       These  join types are just a notational convenience, since
       they do nothing you couldn't do with plain FROM and WHERE.

       sian product (i.e., all combined rows  that  pass  its  ON
       condition),  plus  one  copy  of each row in the left-hand
       table for which there was no right-hand  row  that  passed
       the  ON  condition.  This left-hand row is extended to the
       full width of the joined table by inserting NULLs for  the
       right-hand  columns.   Note that only the JOIN's own ON or
       USING condition is considered while  deciding  which  rows
       have  matches.  Outer  ON  or WHERE conditions are applied
       afterwards.

       Conversely, RIGHT OUTER JOIN returns all the joined  rows,
       plus  one  row for each unmatched right-hand row (extended
       with nulls on the left).  This is just a notational conve­
       nience, since you could convert it to a LEFT OUTER JOIN by
       switching the left and right inputs.

       FULL OUTER JOIN returns all the joined rows, plus one  row
       for  each  unmatched left-hand row (extended with nulls on
       the right), plus one row for each unmatched right-hand row
       (extended with nulls on the left).

       For  all  the JOIN types except CROSS JOIN, you must write
       exactly one of ON join_condition, USING ( join_column_list
       ),  or NATURAL. ON is the most general case: you can write
       any qualification expression involving the two  tables  to
       be joined.  A USING column list ( a, b, ... ) is shorthand
       for the ON  condition  left_table.a  =  right_table.a  AND
       left_table.b = right_table.b ...  Also, USING implies that
       only one of  each  pair  of  equivalent  columns  will  be
       included  in  the JOIN output, not both. NATURAL is short­
       hand for a USING list that  mentions  all  similarly-named
       columns in the tables.

   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

       SELECT will ignore all rows for which the WHERE  condition
       does not return TRUE.

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

       GROUP BY expression [, ...]

       GROUP BY will condense into a single row all selected rows
       that share the same values for the grouped columns. Aggre­
       gate functions, if any, are computed across all rows  mak­
       ing  up  each  group,  producing a separate value for each
       group (whereas without GROUP BY, an aggregate  produces  a
       single  value computed across all the selected rows). When
       GROUP BY is present, it is not valid for the SELECT output
       expression(s)  to refer to ungrouped columns except within
       aggregate functions, since there would be  more  than  one
       possible value to return for an ungrouped column.

       A  GROUP  BY item can be an input column name, or the name
       or ordinal number of an output column (SELECT expression),
       or  it  can  be an arbitrary expression formed from input-
       column values. In case of ambiguity, a GROUP BY name  will
       be interpreted as an input-column name rather than an out­
       put column name.

   HAVING CLAUSE
       The optional HAVING condition has the general form:

       HAVING boolean_expr

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

       HAVING  specifies  a grouped table derived by the elimina­
       tion of group rows that do not satisfy  the  boolean_expr.
       HAVING  is  different from WHERE: WHERE filters individual
       rows before application of GROUP BY, while HAVING  filters
       group rows created by GROUP BY.

       Each column referenced in boolean_expr shall unambiguously
       reference a grouping column, unless the reference  appears
       within an aggregate function.

   ORDER BY CLAUSE
       ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

       output column (SELECT expression), or it can be  an  arbi­
       trary  expression formed from input-column values. In case
       of ambiguity, an ORDER BY name will be interpreted  as  an
       output-column name.

       The  ordinal  number refers to the ordinal (left-to-right)
       position of the result column. This feature makes it  pos­
       sible  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 to assign a name
       to a result column using the AS clause, e.g.:

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

       It is also possible to ORDER BY arbitrary expressions  (an
       extension  to  SQL92), including fields that do not appear
       in the SELECT result list.  Thus the  following  statement
       is legal:

       SELECT name FROM distributors ORDER BY code;

       A  limitation  of  this feature is that an ORDER BY clause
       applying to the result of a UNION,  INTERSECT,  or  EXCEPT
       query  may  only  specify an output column name or number,
       not an expression.

       Note that if an ORDER  BY  item  is  a  simple  name  that
       matches  both  a  result  column  name and an input column
       name, ORDER BY will interpret  it  as  the  result  column
       name.  This  is  the  opposite of the choice that GROUP BY
       will make in the same  situation.  This  inconsistency  is
       mandated by the SQL92 standard.

       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.
       Alternatively, a specific ordering operator  name  may  be
       specified. ASC is equivalent to USING < and DESC is equiv­
       alent to USING >.

   UNION CLAUSE
       table_query UNION [ ALL ] table_query
           [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
           [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

       where table_query specifies any select expression  without
       an  ORDER  BY,  FOR UPDATE, or LIMIT clause. (ORDER BY and
       LIMIT can  be  attached  to  a  sub-expression  if  it  is
       enclosed   in   parentheses.  Without  parentheses,  these

       not to its right-hand input expression.)

       The  UNION operator computes the collection (set union) of
       the rows  returned  by  the  queries  involved.   The  two
       SELECTs  that  represent  the direct operands of the UNION
       must produce the same number of columns, and corresponding
       columns must be of compatible data types.

       The  result  of  UNION does not contain any duplicate rows
       unless the ALL option is specified. ALL prevents  elimina­
       tion of duplicates.

       Multiple  UNION operators in the same SELECT statement are
       evaluated left to right,  unless  otherwise  indicated  by
       parentheses.

       Currently,  FOR  UPDATE  may not be specified either for a
       UNION result or for the inputs of a UNION.

   INTERSECT CLAUSE
       table_query INTERSECT [ ALL ] table_query
           [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
           [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

       where table_query specifies any select expression  without
       an ORDER BY, FOR UPDATE, or LIMIT clause.

       INTERSECT  is  similar  to  UNION, except that it produces
       only rows that appear in both query outputs,  rather  than
       rows that appear in either.

       The  result  of  INTERSECT  does not contain any duplicate
       rows unless the ALL option is specified. With ALL,  a  row
       that  has  m  duplicates  in  L and n duplicates in R will
       appear min(m,n) times.

       Multiple INTERSECT operators in the same SELECT  statement
       are  evaluated  left  to right, unless parentheses dictate
       otherwise.  INTERSECT binds more tightly  than  UNION  ---
       that  is, A UNION B INTERSECT C will be read as A UNION (B
       INTERSECT C) unless otherwise specified by parentheses.

   EXCEPT CLAUSE
       table_query EXCEPT [ ALL ] table_query
           [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
           [ LIMIT { count | ALL } [ { OFFSET | , } start ]]

       where table_query specifies any select expression  without
       an ORDER BY, FOR UPDATE, or LIMIT clause.

       EXCEPT  is  similar to UNION, except that it produces only

       right query's output.

       The  result  of EXCEPT does not contain any duplicate rows
       unless the ALL option is specified. With ALL, a  row  that
       has  m  duplicates  in L and n duplicates in R will appear
       max(m-n,0) times.

       Multiple EXCEPT operators in the same SELECT statement are
       evaluated left to right, unless parentheses dictate other­
       wise.  EXCEPT binds at the same level as UNION.

   LIMIT CLAUSE
           LIMIT { count | ALL } [ { OFFSET | , } start ]
           OFFSET start

       where count  specifies  the  maximum  number  of  rows  to
       return,  and  start  specifies  the number of rows to skip
       before starting to return rows.

       LIMIT allows you to retrieve just a portion  of  the  rows
       that  are  generated  by the rest of the query. If a limit
       count is given, no  more  than  that  many  rows  will  be
       returned.  If  an  offset is given, that many rows will be
       skipped before starting to return rows.

       When using LIMIT, it is a good idea to  use  an  ORDER  BY
       clause  that  constrains  the  result  rows  into a unique
       order. Otherwise you will get an unpredictable  subset  of
       the query's rows---you may be asking for the tenth through
       twentieth rows, but tenth through twentieth in what order­
       ing?  You  don't  know what ordering, unless you specified
       ORDER BY.

       As of Postgres 7.0, the query optimizer takes  LIMIT  into
       account  when  generating  a  query  plan, so you are very
       likely to get  different  plans  (yielding  different  row
       orders)  depending  on what you give for LIMIT and OFFSET.
       Thus, using different LIMIT/OFFSET values to  select  dif­
       ferent  subsets  of  a query result will give inconsistent
       results unless you enforce a predictable  result  ordering
       with ORDER BY. This is not a bug; it is an inherent conse­
       quence of the fact that SQL does not  promise  to  deliver
       the  results  of  a  query  in any particular order unless
       ORDER BY is used to constrain the order.


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

       ---------------------------+-----+------------------+------------+----------
        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
       (17 rows)

       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
       (5 rows)

       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
       (2 rows)

       The  following  two examples are identical ways of sorting

       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
       (13 rows)

       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 wanted, 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

   EXTENSIONS
       Postgres allows one to omit the FROM clause from a  query.
       This feature was retained from the original PostQuel query
       language. It has a  straightforward  use  to  compute  the
       results of simple constant expressions:

       SELECT 2+2;

        ?column?
       ----------
               4

       Some  other  DBMSes cannot do this except by introducing a
       dummy one-row table to do the select from. A less  obvious
       use  is  to  abbreviate  a  normal select from one or more
       tables:

       SELECT distributors.* WHERE name = 'Westward';

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

       This works because an implicit FROM item is added for each
       table that is referenced in the query but not mentioned in
       FROM. While this is a convenient shorthand, it's  easy  to
       misuse. For example, the query

       SELECT distributors.* FROM distributors d;

       is probably a mistake; most likely the user meant

       SELECT d.* FROM distributors d;

       rather than the unconstrained join

       SELECT distributors.* FROM distributors d, distributors distributors;

       that  he  will  actually  get. To help detect this sort of
       mistake, Postgres 7.1 and later will warn if the implicit-
       FROM  feature  is  used  in  a query that also contains an
       explicit FROM clause.

   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

       lead to parsing  ambiguities  in  this  context.  "AS"  is
       optional in FROM items, however.

       The  DISTINCT  ON  phrase  is  not part of SQL92.  Nor are
       LIMIT and OFFSET.

       In SQL92, an ORDER BY clause may only  use  result  column
       names  or  numbers,  while  a GROUP BY clause may only use
       input  column  names.   Postgres  extends  each  of  these
       clauses to allow the other choice as well (but it uses the
       standard's interpretation if there is  ambiguity).   Post­
       gres also allows both clauses to specify arbitrary expres­
       sions. Note that names appearing  in  an  expression  will
       always  be taken as input-column names, not as result-col­
       umn names.

   UNION/INTERSECT/EXCEPT CLAUSE
       The SQL92  syntax  for  UNION/INTERSECT/EXCEPT  allows  an
       additional CORRESPONDING BY option:

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

       The  CORRESPONDING BY clause is not supported by Postgres.


Man(1) output converted with man2html