granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/select.sgml

   1 <!--
   2 $PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.82 2005/03/10 23:21:20 tgl Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-SELECT">
   7  <refmeta>
   8   <refentrytitle id="sql-select-title">SELECT</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>SELECT</refname>
  14   <refpurpose>retrieve rows from a table or view</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-select">
  18   <primary>SELECT</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
  24     * | <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...]
  25     [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
  26     [ WHERE <replaceable class="parameter">condition</replaceable> ]
  27     [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
  28     [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
  29     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
  30     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
  31     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
  32     [ OFFSET <replaceable class="parameter">start</replaceable> ]
  33     [ FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] ]
  34
  35 where <replaceable class="parameter">from_item</replaceable> can be one of:
  36
  37     [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
  38     ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
  39     <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]
  40     <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
  41     <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
  42 </synopsis>
  43
  44  </refsynopsisdiv>
  45
  46  <refsect1>
  47   <title>Description</title>
  48
  49   <para>
  50    <command>SELECT</command> retrieves rows from one or more tables.
  51    The general processing of <command>SELECT</command> is as follows:
  52
  53    <orderedlist>
  54     <listitem>
  55      <para>
  56       All elements in the <literal>FROM</literal> list are computed.
  57       (Each element in the <literal>FROM</literal> list is a real or
  58       virtual table.)  If more than one element is specified in the
  59       <literal>FROM</literal> list, they are cross-joined together.
  60       (See <xref linkend="sql-from" endterm="sql-from-title"> below.)
  61      </para>
  62     </listitem>
  63
  64     <listitem>
  65      <para>
  66       If the <literal>WHERE</literal> clause is specified, all rows
  67       that do not satisfy the condition are eliminated from the
  68       output.  (See <xref linkend="sql-where"
  69       endterm="sql-where-title"> below.)
  70      </para>
  71     </listitem>
  72
  73     <listitem>
  74      <para>
  75       If the <literal>GROUP BY</literal> clause is specified, the
  76       output is divided into groups of rows that match on one or more
  77       values.  If the <literal>HAVING</literal> clause is present, it
  78       eliminates groups that do not satisfy the given condition.  (See
  79       <xref linkend="sql-groupby" endterm="sql-groupby-title"> and
  80       <xref linkend="sql-having" endterm="sql-having-title"> below.)
  81      </para>
  82     </listitem>
  83
  84     <listitem>
  85      <para>
  86       The actual output rows are computed using the
  87       <command>SELECT</command> output expressions for each selected
  88       row.  (See
  89       <xref linkend="sql-select-list" endterm="sql-select-list-title">
  90       below.)
  91      </para>
  92     </listitem>
  93
  94     <listitem>
  95      <para>
  96       Using the operators <literal>UNION</literal>,
  97       <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
  98       output of more than one <command>SELECT</command> statement can
  99       be combined to form a single result set.  The
 100       <literal>UNION</literal> operator returns all rows that are in
 101       one or both of the result sets.  The
 102       <literal>INTERSECT</literal> operator returns all rows that are
 103       strictly in both result sets.  The <literal>EXCEPT</literal>
 104       operator returns the rows that are in the first result set but
 105       not in the second.  In all three cases, duplicate rows are
 106       eliminated unless <literal>ALL</literal> is specified. (See
 107       <xref linkend="sql-union" endterm="sql-union-title">, <xref
 108       linkend="sql-intersect" endterm="sql-intersect-title">, and
 109       <xref linkend="sql-except" endterm="sql-except-title"> below.)
 110      </para>
 111     </listitem>
 112
 113     <listitem>
 114      <para>
 115       If the <literal>ORDER BY</literal> clause is specified, the
 116       returned rows are sorted in the specified order.  If
 117       <literal>ORDER BY</literal> is not given, the rows are returned
 118       in whatever order the system finds fastest to produce.  (See
 119       <xref linkend="sql-orderby" endterm="sql-orderby-title"> below.)
 120      </para>
 121     </listitem>
 122
 123     <listitem>
 124      <para>
 125       <literal>DISTINCT</literal> eliminates duplicate rows from the
 126       result.  <literal>DISTINCT ON</literal> eliminates rows that
 127       match on all the specified expressions.  <literal>ALL</literal>
 128       (the default) will return all candidate rows, including
 129       duplicates.  (See <xref linkend="sql-distinct"
 130       endterm="sql-distinct-title"> below.)
 131      </para>
 132     </listitem>
 133
 134     <listitem>
 135      <para>
 136       If the <literal>LIMIT</literal> or <literal>OFFSET</literal>
 137       clause is specified, the <command>SELECT</command> statement
 138       only returns a subset of the result rows. (See <xref
 139       linkend="sql-limit" endterm="sql-limit-title"> below.)
 140      </para>
 141     </listitem>
 142
 143     <listitem>
 144      <para>
 145       The <literal>FOR UPDATE</literal> clause causes the
 146       <command>SELECT</command> statement to lock the selected rows
 147       against concurrent updates.  (See <xref linkend="sql-for-update"
 148       endterm="sql-for-update-title"> below.)
 149      </para>
 150     </listitem>
 151    </orderedlist>
 152   </para>
 153
 154   <para>
 155    You must have <literal>SELECT</literal> privilege on a table to
 156    read its values.  The use of <literal>FOR UPDATE</literal> requires
 157    <literal>UPDATE</literal> privilege as well.
 158   </para>
 159  </refsect1>
 160
 161  <refsect1>
 162   <title>Parameters</title>
 163
 164   <refsect2 id="SQL-FROM">
 165    <title id="sql-from-title"><literal>FROM</literal> Clause</title>
 166
 167    <para>
 168     The <literal>FROM</literal> clause specifies one or more source
 169     tables for the <command>SELECT</command>.  If multiple sources are
 170     specified, the result is the Cartesian product (cross join) of all
 171     the sources.  But usually qualification conditions
 172     are added to restrict the returned rows to a small subset of the
 173     Cartesian product.
 174    </para>
 175
 176    <para>
 177     The <literal>FROM</literal> clause can contain the following
 178     elements:
 179
 180     <variablelist>
 181      <varlistentry>
 182       <term><replaceable class="parameter">table_name</replaceable></term>
 183       <listitem>
 184        <para>
 185         The name (optionally schema-qualified) of an existing table or
 186         view.  If <literal>ONLY</> is specified, only that table is
 187         scanned.  If <literal>ONLY</> is not specified, the table and
 188         all its descendant tables (if any) are scanned.  <literal>*</>
 189         can be appended to the table name to indicate that descendant
 190         tables are to be scanned, but in the current version, this is
 191         the default behavior.  (In releases before 7.1,
 192         <literal>ONLY</> was the default behavior.)  The default
 193         behavior can be modified by changing the <xref
 194         linkend="guc-sql-inheritance"> configuration option.
 195        </para>
 196       </listitem>
 197      </varlistentry>
 198
 199      <varlistentry>
 200       <term><replaceable class="parameter">alias</replaceable></term>
 201       <listitem>
 202        <para>
 203         A substitute name for the <literal>FROM</> item containing the
 204         alias.  An alias is used for brevity or to eliminate ambiguity
 205         for self-joins (where the same table is scanned multiple
 206         times).  When an alias is provided, it completely hides the
 207         actual name of the table or function; for example given
 208         <literal>FROM foo AS f</>, the remainder of the
 209         <command>SELECT</command> must refer to this <literal>FROM</>
 210         item as <literal>f</> not <literal>foo</>.  If an alias is
 211         written, a column alias list can also be written to provide
 212         substitute names for one or more columns of the table.
 213        </para>
 214       </listitem>
 215      </varlistentry>
 216
 217      <varlistentry>
 218       <term><replaceable class="parameter">select</replaceable></term>
 219       <listitem>
 220        <para>
 221         A sub-<command>SELECT</command> can appear in the
 222         <literal>FROM</literal> clause.  This acts as though its
 223         output were created as a temporary table for the duration of
 224         this single <command>SELECT</command> command.  Note that the
 225         sub-<command>SELECT</command> must be surrounded by
 226         parentheses, and an alias <emphasis>must</emphasis> be
 227         provided for it.
 228        </para>
 229       </listitem>
 230      </varlistentry>
 231
 232      <varlistentry>
 233       <term><replaceable class="parameter">function_name</replaceable></term>
 234       <listitem>
 235        <para>
 236         Function calls can appear in the <literal>FROM</literal>
 237         clause.  (This is especially useful for functions that return
 238         result sets, but any function can be used.)  This acts as
 239         though its output were created as a temporary table for the
 240         duration of this single <command>SELECT</command> command. An
 241         alias may also be used. If an alias is written, a column alias
 242         list can also be written to provide substitute names for one
 243         or more attributes of the function's composite return type. If
 244         the function has been defined as returning the <type>record</>
 245         data type, then an alias or the key word <literal>AS</> must
 246         be present, followed by a column definition list in the form
 247         <literal>( <replaceable
 248         class="parameter">column_name</replaceable> <replaceable
 249         class="parameter">data_type</replaceable> <optional>, ... </>
 250         )</literal>.  The column definition list must match the actual
 251         number and types of columns returned by the function.
 252        </para>
 253       </listitem>
 254      </varlistentry>
 255
 256      <varlistentry>
 257       <term><replaceable class="parameter">join_type</replaceable></term>
 258       <listitem>
 259        <para>
 260         One of
 261         <itemizedlist>
 262          <listitem>
 263           <para><literal>[ INNER ] JOIN</literal></para>
 264          </listitem>
 265          <listitem>
 266           <para><literal>LEFT [ OUTER ] JOIN</literal></para>
 267          </listitem>
 268          <listitem>
 269           <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
 270          </listitem>
 271          <listitem>
 272           <para><literal>FULL [ OUTER ] JOIN</literal></para>
 273          </listitem>
 274          <listitem>
 275           <para><literal>CROSS JOIN</literal></para>
 276          </listitem>
 277         </itemizedlist>
 278
 279         For the <literal>INNER</> and <literal>OUTER</> join types, a
 280         join condition must be specified, namely exactly one of
 281         <literal>NATURAL</>, <literal>ON <replaceable
 282         class="parameter">join_condition</replaceable></literal>, or
 283         <literal>USING (<replaceable
 284         class="parameter">join_column</replaceable> [, ...])</literal>.
 285         See below for the meaning.  For <literal>CROSS JOIN</literal>,
 286         none of these clauses may appear.
 287        </para>
 288
 289        <para>
 290         A <literal>JOIN</literal> clause combines two
 291         <literal>FROM</> items.  Use parentheses if necessary to
 292         determine the order of nesting.  In the absence of parentheses,
 293         <literal>JOIN</literal>s nest left-to-right.  In any case
 294         <literal>JOIN</literal> binds more tightly than the commas
 295         separating <literal>FROM</> items.
 296        </para>
 297
 298        <para>
 299         <literal>CROSS JOIN</> and <literal>INNER JOIN</literal>
 300         produce a simple Cartesian product, the same result as you get from
 301         listing the two items at the top level of <literal>FROM</>,
 302         but restricted by the join condition (if any).
 303         <literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON
 304         (TRUE)</>, that is, no rows are removed by qualification.
 305         These join types are just a notational convenience, since they
 306         do nothing you couldn't do with plain <literal>FROM</> and
 307         <literal>WHERE</>.
 308        </para>
 309
 310        <para>
 311         <literal>LEFT OUTER JOIN</> returns all rows in the qualified
 312         Cartesian product (i.e., all combined rows that pass its join
 313         condition), plus one copy of each row in the left-hand table
 314         for which there was no right-hand row that passed the join
 315         condition.  This left-hand row is extended to the full width
 316         of the joined table by inserting null values for the
 317         right-hand columns.  Note that only the <literal>JOIN</>
 318         clause's own condition is considered while deciding which rows
 319         have matches.  Outer conditions are applied afterwards.
 320        </para>
 321
 322        <para>
 323         Conversely, <literal>RIGHT OUTER JOIN</> returns all the
 324         joined rows, plus one row for each unmatched right-hand row
 325         (extended with nulls on the left).  This is just a notational
 326         convenience, since you could convert it to a <literal>LEFT
 327         OUTER JOIN</> by switching the left and right inputs.
 328        </para>
 329
 330        <para>
 331         <literal>FULL OUTER JOIN</> returns all the joined rows, plus
 332         one row for each unmatched left-hand row (extended with nulls
 333         on the right), plus one row for each unmatched right-hand row
 334         (extended with nulls on the left).
 335        </para>
 336       </listitem>
 337      </varlistentry>
 338
 339      <varlistentry>
 340       <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>
 341       <listitem>
 342        <para>
 343         <replaceable class="parameter">join_condition</replaceable> is
 344         an expression resulting in a value of type
 345         <type>boolean</type> (similar to a <literal>WHERE</literal>
 346         clause) that specifies which rows in a join are considered to
 347         match.
 348        </para>
 349       </listitem>
 350      </varlistentry>
 351
 352      <varlistentry>
 353       <term><literal>USING (<replaceable class="parameter">join_column</replaceable> [, ...])</literal></term>
 354       <listitem>
 355        <para>
 356         A clause of the form <literal>USING ( a, b, ... )</literal> is
 357         shorthand for <literal>ON left_table.a = right_table.a AND
 358         left_table.b = right_table.b ...</literal>.  Also,
 359         <literal>USING</> implies that only one of each pair of
 360         equivalent columns will be included in the join output, not
 361         both.
 362        </para>
 363       </listitem>
 364      </varlistentry>
 365
 366      <varlistentry>
 367       <term><literal>NATURAL</literal></term>
 368       <listitem>
 369        <para>
 370         <literal>NATURAL</literal> is shorthand for a
 371         <literal>USING</> list that mentions all columns in the two
 372         tables that have the same names.
 373        </para>
 374       </listitem>
 375      </varlistentry>
 376     </variablelist>
 377    </para>
 378   </refsect2>
 379
 380   <refsect2 id="SQL-WHERE">
 381    <title id="sql-where-title"><literal>WHERE</literal> Clause</title>
 382
 383    <para>
 384     The optional <literal>WHERE</literal> clause has the general form
 385 <synopsis>
 386 WHERE <replaceable class="parameter">condition</replaceable>
 387 </synopsis>
 388     where <replaceable class="parameter">condition</replaceable> is
 389     any expression that evaluates to a result of type
 390     <type>boolean</type>.  Any row that does not satisfy this
 391     condition will be eliminated from the output.  A row satisfies the
 392     condition if it returns true when the actual row values are
 393     substituted for any variable references.
 394    </para>
 395   </refsect2>
 396
 397   <refsect2 id="SQL-GROUPBY">
 398    <title id="sql-groupby-title"><literal>GROUP BY</literal> Clause</title>
 399
 400    <para>
 401     The optional <literal>GROUP BY</literal> clause has the general form
 402 <synopsis>
 403 GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
 404 </synopsis>
 405    </para>
 406
 407    <para>
 408     <literal>GROUP BY</literal> will condense into a single row all
 409     selected rows that share the same values for the grouped
 410     expressions.  <replaceable
 411     class="parameter">expression</replaceable> can be an input column
 412     name, or the name or ordinal number of an output column
 413     (<command>SELECT</command> list item), or an arbitrary
 414     expression formed from input-column values.  In case of ambiguity,
 415     a <literal>GROUP BY</literal> name will be interpreted as an
 416     input-column name rather than an output column name.
 417    </para>
 418
 419    <para>
 420     Aggregate functions, if any are used, are computed across all rows
 421     making up each group, producing a separate value for each group
 422     (whereas without <literal>GROUP BY</literal>, an aggregate
 423     produces a single value computed across all the selected rows).
 424     When <literal>GROUP BY</literal> is present, it is not valid for
 425     the <command>SELECT</command> list expressions to refer to
 426     ungrouped columns except within aggregate functions, since there
 427     would be more than one possible value to return for an ungrouped
 428     column.
 429    </para>
 430   </refsect2>
 431
 432   <refsect2 id="SQL-HAVING">
 433    <title id="sql-having-title"><literal>HAVING</literal> Clause</title>
 434
 435    <para>
 436     The optional <literal>HAVING</literal> clause has the general form
 437 <synopsis>
 438 HAVING <replaceable class="parameter">condition</replaceable>
 439 </synopsis>
 440     where <replaceable class="parameter">condition</replaceable> is
 441     the same as specified for the <literal>WHERE</literal> clause.
 442    </para>
 443
 444    <para>
 445     <literal>HAVING</literal> eliminates group rows that do not
 446     satisfy the condition.  <literal>HAVING</literal> is different
 447     from <literal>WHERE</literal>: <literal>WHERE</literal> filters
 448     individual rows before the application of <literal>GROUP
 449     BY</literal>, while <literal>HAVING</literal> filters group rows
 450     created by <literal>GROUP BY</literal>.  Each column referenced in
 451     <replaceable class="parameter">condition</replaceable> must
 452     unambiguously reference a grouping column, unless the reference
 453     appears within an aggregate function.
 454    </para>
 455
 456    <para>
 457     The presence of <literal>HAVING</literal> turns a query into a grouped
 458     query even if there is no <literal>GROUP BY</> clause.  This is the
 459     same as what happens when the query contains aggregate functions but
 460     no <literal>GROUP BY</> clause.  All the selected rows are considered to
 461     form a single group, and the <command>SELECT</command> list and
 462     <literal>HAVING</literal> clause can only reference table columns from
 463     within aggregate functions.  Such a query will emit a single row if the
 464     <literal>HAVING</literal> condition is true, zero rows if it is not true.
 465    </para>
 466   </refsect2>
 467
 468   <refsect2 id="sql-select-list">
 469    <title id="sql-select-list-title"><command>SELECT</command> List</title>
 470
 471    <para>
 472     The <command>SELECT</command> list (between the key words
 473     <literal>SELECT</> and <literal>FROM</>) specifies expressions
 474     that form the output rows of the <command>SELECT</command>
 475     statement.  The expressions can (and usually do) refer to columns
 476     computed in the <literal>FROM</> clause.  Using the clause
 477     <literal>AS <replaceable
 478     class="parameter">output_name</replaceable></literal>, another
 479     name can be specified for an output column.  This name is
 480     primarily used to label the column for display.  It can also be
 481     used to refer to the column's value in <literal>ORDER BY</> and
 482     <literal>GROUP BY</> clauses, but not in the <literal>WHERE</> or
 483     <literal>HAVING</> clauses; there you must write out the
 484     expression instead.
 485    </para>
 486
 487    <para>
 488     Instead of an expression, <literal>*</literal> can be written in
 489     the output list as a shorthand for all the columns of the selected
 490     rows.  Also, one can write <literal><replaceable
 491     class="parameter">table_name</replaceable>.*</literal> as a
 492     shorthand for the columns coming from just that table.
 493    </para>
 494   </refsect2>
 495
 496   <refsect2 id="SQL-UNION">
 497    <title id="sql-union-title"><literal>UNION</literal> Clause</title>
 498
 499    <para>
 500     The <literal>UNION</literal> clause has this general form:
 501 <synopsis>
 502 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 503 </synopsis>
 504     <replaceable class="parameter">select_statement</replaceable> is
 505     any <command>SELECT</command> statement without an <literal>ORDER
 506     BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
 507     (<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
 508     subexpression if it is enclosed in parentheses.  Without
 509     parentheses, these clauses will be taken to apply to the result of
 510     the <literal>UNION</literal>, not to its right-hand input
 511     expression.)
 512    </para>
 513
 514    <para>
 515     The <literal>UNION</literal> operator computes the set union of
 516     the rows returned by the involved <command>SELECT</command>
 517     statements.  A row is in the set union of two result sets if it
 518     appears in at least one of the result sets.  The two
 519     <command>SELECT</command> statements that represent the direct
 520     operands of the <literal>UNION</literal> must produce the same
 521     number of columns, and corresponding columns must be of compatible
 522     data types.
 523    </para>
 524
 525    <para>
 526     The result of <literal>UNION</> does not contain any duplicate
 527     rows unless the <literal>ALL</> option is specified.
 528     <literal>ALL</> prevents elimination of duplicates.  (Therefore,
 529     <literal>UNION ALL</> is usually significantly quicker than
 530     <literal>UNION</>; use <literal>ALL</> when you can.)
 531    </para>
 532
 533    <para>
 534     Multiple <literal>UNION</> operators in the same
 535     <command>SELECT</command> statement are evaluated left to right,
 536     unless otherwise indicated by parentheses.
 537    </para>
 538
 539    <para>
 540     Currently, <literal>FOR UPDATE</> may not be specified either for
 541     a <literal>UNION</> result or for any input of a <literal>UNION</>.
 542    </para>
 543   </refsect2>
 544
 545   <refsect2 id="SQL-INTERSECT">
 546    <title id="sql-intersect-title"><literal>INTERSECT</literal> Clause</title>
 547
 548    <para>
 549     The <literal>INTERSECT</literal> clause has this general form:
 550 <synopsis>
 551 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 552 </synopsis>
 553     <replaceable class="parameter">select_statement</replaceable> is
 554     any <command>SELECT</command> statement without an <literal>ORDER
 555     BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
 556    </para>
 557
 558    <para>
 559     The <literal>INTERSECT</literal> operator computes the set
 560     intersection of the rows returned by the involved
 561     <command>SELECT</command> statements.  A row is in the
 562     intersection of two result sets if it appears in both result sets.
 563    </para>
 564
 565    <para>
 566     The result of <literal>INTERSECT</literal> does not contain any
 567     duplicate rows unless the <literal>ALL</> option is specified.
 568     With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
 569     left table and <replaceable>n</> duplicates in the right table will appear
 570     min(<replaceable>m</>,<replaceable>n</>) times in the result set.
 571    </para>
 572
 573    <para>
 574     Multiple <literal>INTERSECT</literal> operators in the same
 575     <command>SELECT</command> statement are evaluated left to right,
 576     unless parentheses dictate otherwise.
 577     <literal>INTERSECT</literal> binds more tightly than
 578     <literal>UNION</literal>.  That is, <literal>A UNION B INTERSECT
 579     C</literal> will be read as <literal>A UNION (B INTERSECT
 580     C)</literal>.
 581    </para>
 582
 583    <para>
 584     Currently, <literal>FOR UPDATE</> may not be specified either for
 585     an <literal>INTERSECT</> result or for any input of an <literal>INTERSECT</>.
 586    </para>
 587   </refsect2>
 588
 589   <refsect2 id="SQL-EXCEPT">
 590    <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>
 591
 592    <para>
 593     The <literal>EXCEPT</literal> clause has this general form:
 594 <synopsis>
 595 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
 596 </synopsis>
 597     <replaceable class="parameter">select_statement</replaceable> is
 598     any <command>SELECT</command> statement without an <literal>ORDER
 599     BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
 600    </para>
 601
 602    <para>
 603     The <literal>EXCEPT</literal> operator computes the set of rows
 604     that are in the result of the left <command>SELECT</command>
 605     statement but not in the result of the right one.
 606    </para>
 607
 608    <para>
 609     The result of <literal>EXCEPT</literal> does not contain any
 610     duplicate rows unless the <literal>ALL</> option is specified.
 611     With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
 612     left table and <replaceable>n</> duplicates in the right table will appear
 613     max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
 614    </para>
 615
 616    <para>
 617     Multiple <literal>EXCEPT</literal> operators in the same
 618     <command>SELECT</command> statement are evaluated left to right,
 619     unless parentheses dictate otherwise.  <literal>EXCEPT</> binds at
 620     the same level as <literal>UNION</>.
 621    </para>
 622
 623    <para>
 624     Currently, <literal>FOR UPDATE</> may not be specified either for
 625     an <literal>EXCEPT</> result or for any input of an <literal>EXCEPT</>.
 626    </para>
 627   </refsect2>
 628
 629   <refsect2 id="SQL-ORDERBY">
 630    <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>
 631
 632    <para>
 633     The optional <literal>ORDER BY</literal> clause has this general form:
 634 <synopsis>
 635 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...]
 636 </synopsis>
 637     <replaceable class="parameter">expression</replaceable> can be the
 638     name or ordinal number of an output column
 639     (<command>SELECT</command> list item), or it can be an arbitrary
 640     expression formed from input-column values.
 641    </para>
 642
 643    <para>
 644     The <literal>ORDER BY</literal> clause causes the result rows to
 645     be sorted according to the specified expressions.  If two rows are
 646     equal according to the leftmost expression, the are compared
 647     according to the next expression and so on.  If they are equal
 648     according to all specified expressions, they are returned in
 649     an implementation-dependent order.
 650    </para>
 651
 652    <para>
 653     The ordinal number refers to the ordinal (left-to-right) position
 654     of the result column. This feature makes it possible to define an
 655     ordering on the basis of a column that does not have a unique
 656     name.  This is never absolutely necessary because it is always
 657     possible to assign a name to a result column using the
 658     <literal>AS</> clause.
 659    </para>
 660
 661    <para>
 662     It is also possible to use arbitrary expressions in the
 663     <literal>ORDER BY</literal> clause, including columns that do not
 664     appear in the <command>SELECT</command> result list.  Thus the
 665     following statement is valid:
 666 <programlisting>
 667 SELECT name FROM distributors ORDER BY code;
 668 </programlisting>
 669     A limitation of this feature is that an <literal>ORDER BY</>
 670     clause applying to the result of a <literal>UNION</>,
 671     <literal>INTERSECT</>, or <literal>EXCEPT</> clause may only
 672     specify an output column name or number, not an expression.
 673    </para>
 674
 675    <para>
 676     If an <literal>ORDER BY</> expression is a simple name that
 677     matches both a result column name and an input column name,
 678     <literal>ORDER BY</> will interpret it as the result column name.
 679     This is the opposite of the choice that <literal>GROUP BY</> will
 680     make in the same situation.  This inconsistency is made to be
 681     compatible with the SQL standard.
 682    </para>
 683
 684    <para>
 685     Optionally one may add the key word <literal>ASC</> (ascending) or
 686     <literal>DESC</> (descending) after any expression in the
 687     <literal>ORDER BY</> clause.  If not specified, <literal>ASC</> is
 688     assumed by default.  Alternatively, a specific ordering operator
 689     name may be specified in the <literal>USING</> clause.
 690     <literal>ASC</> is usually equivalent to <literal>USING &lt;</> and
 691     <literal>DESC</> is usually equivalent to <literal>USING &gt;</>.
 692     (But the creator of a user-defined data type can define exactly what the
 693     default sort ordering is, and it might correspond to operators with other
 694     names.)
 695    </para>
 696
 697    <para>
 698     The null value sorts higher than any other value. In other words,
 699     with ascending sort order, null values sort at the end, and with
 700     descending sort order, null values sort at the beginning.
 701    </para>
 702
 703    <para>
 704     Character-string data is sorted according to the locale-specific
 705     collation order that was established when the database cluster
 706     was initialized.
 707    </para>
 708   </refsect2>
 709
 710   <refsect2 id="sql-distinct">
 711    <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
 712
 713    <para>
 714     If <literal>DISTINCT</> is specified, all duplicate rows are
 715     removed from the result set (one row is kept from each group of
 716     duplicates).  <literal>ALL</> specifies the opposite: all rows are
 717     kept; that is the default.
 718    </para>
 719
 720    <para>
 721     <literal>DISTINCT ON ( <replaceable
 722     class="parameter">expression</replaceable> [, ...] )</literal>
 723     keeps only the first row of each set of rows where the given
 724     expressions evaluate to equal.  The <literal>DISTINCT ON</literal>
 725     expressions are interpreted using the same rules as for
 726     <literal>ORDER BY</> (see above).  Note that the <quote>first
 727     row</quote> of each set is unpredictable unless <literal>ORDER
 728     BY</> is used to ensure that the desired row appears first.  For
 729     example,
 730 <programlisting>
 731 SELECT DISTINCT ON (location) location, time, report
 732     FROM weather_reports
 733     ORDER BY location, time DESC;
 734 </programlisting>
 735     retrieves the most recent weather report for each location.  But
 736     if we had not used <literal>ORDER BY</> to force descending order
 737     of time values for each location, we'd have gotten a report from
 738     an unpredictable time for each location.
 739    </para>
 740
 741    <para>
 742     The <literal>DISTINCT ON</> expression(s) must match the leftmost
 743     <literal>ORDER BY</> expression(s).  The <literal>ORDER BY</> clause
 744     will normally contain additional expression(s) that determine the
 745     desired precedence of rows within each <literal>DISTINCT ON</> group.
 746    </para>
 747   </refsect2>
 748
 749   <refsect2 id="SQL-LIMIT">
 750    <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
 751
 752    <para>
 753     The <literal>LIMIT</literal> clause consists of two independent
 754     sub-clauses:
 755 <synopsis>
 756 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
 757 OFFSET <replaceable class="parameter">start</replaceable>
 758 </synopsis>
 759     <replaceable class="parameter">count</replaceable> specifies the
 760     maximum number of rows to return, while <replaceable
 761     class="parameter">start</replaceable> specifies the number of rows
 762     to skip before starting to return rows.  When both are specified,
 763     <replaceable class="parameter">start</replaceable> rows are skipped
 764     before starting to count the <replaceable
 765     class="parameter">count</replaceable> rows to be returned.
 766    </para>
 767
 768    <para>
 769     When using <literal>LIMIT</>, it is a good idea to use an
 770     <literal>ORDER BY</> clause that constrains the result rows into a
 771     unique order.  Otherwise you will get an unpredictable subset of
 772     the query's rows &mdash; you may be asking for the tenth through
 773     twentieth rows, but tenth through twentieth in what ordering?  You
 774     don't know what ordering unless you specify <literal>ORDER BY</>.
 775    </para>
 776
 777    <para>
 778     The query planner takes <literal>LIMIT</> into account when
 779     generating a query plan, so you are very likely to get different
 780     plans (yielding different row orders) depending on what you use
 781     for <literal>LIMIT</> and <literal>OFFSET</>.  Thus, using
 782     different <literal>LIMIT</>/<literal>OFFSET</> values to select
 783     different subsets of a query result <emphasis>will give
 784     inconsistent results</emphasis> unless you enforce a predictable
 785     result ordering with <literal>ORDER BY</>.  This is not a bug; it
 786     is an inherent consequence of the fact that SQL does not promise
 787     to deliver the results of a query in any particular order unless
 788     <literal>ORDER BY</> is used to constrain the order.
 789    </para>
 790   </refsect2>
 791
 792   <refsect2 id="SQL-FOR-UPDATE">
 793    <title id="sql-for-update-title"><literal>FOR UPDATE</literal> Clause</title>
 794
 795    <para>
 796     The <literal>FOR UPDATE</literal> clause has this form:
 797 <synopsis>
 798 FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
 799 </synopsis>
 800    </para>
 801
 802    <para>
 803     <literal>FOR UPDATE</literal> causes the rows retrieved by the
 804     <command>SELECT</command> statement to be locked as though for
 805     update.  This prevents them from being modified or deleted by
 806     other transactions until the current transaction ends.  That is,
 807     other transactions that attempt <command>UPDATE</command>,
 808     <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
 809     of these rows will be blocked until the current transaction ends.
 810     Also, if an <command>UPDATE</command>, <command>DELETE</command>,
 811     or <command>SELECT FOR UPDATE</command> from another transaction
 812     has already locked a selected row or rows, <command>SELECT FOR
 813     UPDATE</command> will wait for the other transaction to complete,
 814     and will then lock and return the updated row (or no row, if the
 815     row was deleted).  For further discussion see <xref
 816     linkend="mvcc">.
 817    </para>
 818
 819    <para>
 820     If specific tables are named in <literal>FOR UPDATE</literal>,
 821     then only rows coming from those tables are locked; any other
 822     tables used in the <command>SELECT</command> are simply read as
 823     usual.
 824    </para>
 825
 826    <para>
 827     <literal>FOR UPDATE</literal> cannot be used in contexts where
 828     returned rows can't be clearly identified with individual table
 829     rows; for example it can't be used with aggregation.
 830    </para>
 831
 832    <para>
 833     <literal>FOR UPDATE</literal> may appear before
 834     <literal>LIMIT</literal> for compatibility with
 835     <productname>PostgreSQL</productname> versions before 7.3.  It
 836     effectively executes after <literal>LIMIT</literal>, however, and
 837     so that is the recommended place to write it.
 838    </para>
 839   </refsect2>
 840  </refsect1>
 841
 842  <refsect1>
 843   <title>Examples</title>
 844
 845   <para>
 846    To join the table <literal>films</literal> with the table
 847    <literal>distributors</literal>:
 848
 849 <programlisting>
 850 SELECT f.title, f.did, d.name, f.date_prod, f.kind
 851     FROM distributors d, films f
 852     WHERE f.did = d.did
 853
 854        title       | did |     name     | date_prod  |   kind
 855 -------------------+-----+--------------+------------+----------
 856  The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 857  The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 858  ...
 859 </programlisting>
 860   </para>
 861
 862   <para>
 863    To sum the column <literal>len</literal> of all films and group
 864    the results by <literal>kind</literal>:
 865
 866 <programlisting>
 867 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
 868
 869    kind   | total
 870 ----------+-------
 871  Action   | 07:34
 872  Comedy   | 02:58
 873  Drama    | 14:28
 874  Musical  | 06:42
 875  Romantic | 04:38
 876 </programlisting>
 877   </para>
 878
 879   <para>
 880    To sum the column <literal>len</literal> of all films, group
 881    the results by <literal>kind</literal> and show those group totals
 882    that are less than 5 hours:
 883
 884 <programlisting>
 885 SELECT kind, sum(len) AS total
 886     FROM films
 887     GROUP BY kind
 888     HAVING sum(len) &lt; interval '5 hours';
 889
 890    kind   | total
 891 ----------+-------
 892  Comedy   | 02:58
 893  Romantic | 04:38
 894 </programlisting>
 895   </para>
 896
 897   <para>
 898    The following two examples are identical ways of sorting the individual
 899    results according to the contents of the second column
 900    (<literal>name</literal>):
 901
 902 <programlisting>
 903 SELECT * FROM distributors ORDER BY name;
 904 SELECT * FROM distributors ORDER BY 2;
 905
 906  did |       name
 907 -----+------------------
 908  109 | 20th Century Fox
 909  110 | Bavaria Atelier
 910  101 | British Lion
 911  107 | Columbia
 912  102 | Jean Luc Godard
 913  113 | Luso films
 914  104 | Mosfilm
 915  103 | Paramount
 916  106 | Toho
 917  105 | United Artists
 918  111 | Walt Disney
 919  112 | Warner Bros.
 920  108 | Westward
 921 </programlisting>
 922   </para>
 923
 924   <para>
 925    The next example shows how to obtain the union of the tables
 926    <literal>distributors</literal> and
 927    <literal>actors</literal>, restricting the results to those that begin
 928    with the letter W in each table.  Only distinct rows are wanted, so the
 929    key word <literal>ALL</literal> is omitted.
 930
 931 <programlisting>
 932 distributors:               actors:
 933  did |     name              id |     name
 934 -----+--------------        ----+----------------
 935  108 | Westward               1 | Woody Allen
 936  111 | Walt Disney            2 | Warren Beatty
 937  112 | Warner Bros.           3 | Walter Matthau
 938  ...                         ...
 939
 940 SELECT distributors.name
 941     FROM distributors
 942     WHERE distributors.name LIKE 'W%'
 943 UNION
 944 SELECT actors.name
 945     FROM actors
 946     WHERE actors.name LIKE 'W%';
 947
 948       name
 949 ----------------
 950  Walt Disney
 951  Walter Matthau
 952  Warner Bros.
 953  Warren Beatty
 954  Westward
 955  Woody Allen
 956 </programlisting>
 957   </para>
 958
 959   <para>
 960    This example shows how to use a function in the <literal>FROM</>
 961    clause, both with and without a column definition list:
 962
 963 <programlisting>
 964 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
 965     SELECT * FROM distributors WHERE did = $1;
 966 $$ LANGUAGE SQL;
 967
 968 SELECT * FROM distributors(111);
 969  did |    name
 970 -----+-------------
 971  111 | Walt Disney
 972
 973 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
 974     SELECT * FROM distributors WHERE did = $1;
 975 $$ LANGUAGE SQL;
 976
 977 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 978  f1  |     f2
 979 -----+-------------
 980  111 | Walt Disney
 981 </programlisting>
 982   </para>
 983  </refsect1>
 984
 985  <refsect1>
 986   <title>Compatibility</title>
 987
 988   <para>
 989    Of course, the <command>SELECT</command> statement is compatible
 990    with the SQL standard.  But there are some extensions and some
 991    missing features.
 992   </para>
 993
 994   <refsect2>
 995    <title>Omitted <literal>FROM</literal> Clauses</title>
 996
 997    <para>
 998     <productname>PostgreSQL</productname> allows one to omit the
 999     <literal>FROM</literal> clause.  It has a straightforward use to
1000     compute the results of simple expressions:
1001 <programlisting>
1002 SELECT 2+2;
1003
1004  ?column?
1005 ----------
1006         4
1007 </programlisting>
1008     Some other <acronym>SQL</acronym> databases cannot do this except
1009     by introducing a dummy one-row table from which to do the
1010     <command>SELECT</command>.
1011    </para>
1012
1013    <para>
1014     A less obvious use is to abbreviate a normal
1015     <command>SELECT</command> from tables:
1016 <programlisting>
1017 SELECT distributors.* WHERE distributors.name = 'Westward';
1018
1019  did |   name
1020 -----+----------
1021  108 | Westward
1022 </programlisting>
1023     This works because an implicit <literal>FROM</literal> item is
1024     added for each table that is referenced in other parts of the
1025     <command>SELECT</command> statement but not mentioned in
1026     <literal>FROM</literal>.
1027    </para>
1028
1029    <para>
1030     While this is a convenient shorthand, it's easy to misuse.  For
1031     example, the command
1032 <programlisting>
1033 SELECT distributors.* FROM distributors d;
1034 </programlisting>
1035     is probably a mistake; most likely the user meant
1036 <programlisting>
1037 SELECT d.* FROM distributors d;
1038 </programlisting>
1039     rather than the unconstrained join
1040 <programlisting>
1041 SELECT distributors.* FROM distributors d, distributors distributors;
1042 </programlisting>
1043     that he will actually get.  To help detect this sort of mistake,
1044     <productname>PostgreSQL</productname> will warn if the
1045     implicit-<literal>FROM</literal> feature is used in a
1046     <command>SELECT</command> statement that also contains an explicit
1047     <literal>FROM</literal> clause.  Also, it is possible to disable
1048     the implicit-<literal>FROM</literal> feature by setting the
1049     <xref linkend="guc-add-missing-from"> parameter to false.
1050    </para>
1051   </refsect2>
1052
1053   <refsect2>
1054    <title>The <literal>AS</literal> Key Word</title>
1055
1056    <para>
1057     In the SQL standard, the optional key word <literal>AS</> is just
1058     noise and can be omitted without affecting the meaning.  The
1059     <productname>PostgreSQL</productname> parser requires this key
1060     word when renaming output columns because the type extensibility
1061     features lead to parsing ambiguities without it.
1062     <literal>AS</literal> is optional in <literal>FROM</literal>
1063     items, however.
1064    </para>
1065   </refsect2>
1066
1067   <refsect2>
1068    <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
1069
1070    <para>
1071     In the SQL-92 standard, an <literal>ORDER BY</literal> clause may
1072     only use result column names or numbers, while a <literal>GROUP
1073     BY</literal> clause may only use expressions based on input column
1074     names.  <productname>PostgreSQL</productname> extends each of
1075     these clauses to allow the other choice as well (but it uses the
1076     standard's interpretation if there is ambiguity).
1077     <productname>PostgreSQL</productname> also allows both clauses to
1078     specify arbitrary expressions.  Note that names appearing in an
1079     expression will always be taken as input-column names, not as
1080     result-column names.
1081    </para>
1082
1083    <para>
1084     SQL:1999 uses a slightly different definition which is not entirely upward
1085     compatible
1086     with SQL-92.  In most cases, however, <productname>PostgreSQL</productname>
1087     will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1088     BY</literal> expression the same way SQL:1999 does.
1089    </para>
1090   </refsect2>
1091
1092   <refsect2>
1093    <title>Nonstandard Clauses</title>
1094
1095    <para>
1096     The clauses <literal>DISTINCT ON</literal>,
1097     <literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
1098     defined in the SQL standard.
1099    </para>
1100   </refsect2>
1101  </refsect1>
1102 </refentry>
1103
1104 <!-- Keep this comment at the end of the file
1105 Local variables:
1106 mode: sgml
1107 sgml-omittag:nil
1108 sgml-shorttag:t
1109 sgml-minimize-attributes:nil
1110 sgml-always-quote-attributes:t
1111 sgml-indent-step:1
1112 sgml-indent-data:t
1113 sgml-parent-document:nil
1114 sgml-default-dtd-file:"../reference.ced"
1115 sgml-exposed-tags:nil
1116 sgml-local-catalogs:"/usr/lib/sgml/catalog"
1117 sgml-local-ecat-files:nil
1118 End:
1119 -->