2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.68 2003/08/17 22:09:00 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-SELECT">
8 <refentrytitle id="sql-select-title">SELECT</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>SELECT</refname>
14 <refpurpose>retrieve rows from a table or view</refpurpose>
19 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
20 * | <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...]
21 [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
22 [ WHERE <replaceable class="parameter">condition</replaceable> ]
23 [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
24 [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
25 [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
26 [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
27 [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
28 [ OFFSET <replaceable class="parameter">start</replaceable> ]
29 [ FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] ]
31 where <replaceable class="parameter">from_item</replaceable> can be one of:
33 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
34 ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
35 <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> [, ...] ) ]
36 <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
37 <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> [, ...] ) ]
40 <comment>FIXME: This last syntax is incorrect if the join type is an
41 INNER or OUTER join (in which case one of NATURAL, ON ..., or USING
42 ... is mandatory, not optional). What's the best way to fix
48 <title>Description</title>
51 <command>SELECT</command> retrieves rows from one or more tables.
52 The general processing of <command>SELECT</command> is as follows:
57 All elements in the <literal>FROM</literal> list are computed.
58 (Each element in the <literal>FROM</literal> list is a real or
59 virtual table.) If more than one element is specified in the
60 <literal>FROM</literal> list, they are cross-joined together.
61 (See <xref linkend="sql-from" endterm="sql-from-title"> below.)
67 If the <literal>WHERE</literal> clause is specified, all rows
68 that do not satisfy the condition are eliminated from the
69 output. (See <xref linkend="sql-where"
70 endterm="sql-where-title"> below.)
76 If the <literal>GROUP BY</literal> clause is specified, the
77 output is divided into groups of rows that match on one or more
78 values. If the <literal>HAVING</literal> clause is present, it
79 eliminates groups that do not satisfy the given condition. (See
80 <xref linkend="sql-groupby" endterm="sql-groupby-title"> and
81 <xref linkend="sql-having" endterm="sql-having-title"> below.)
87 Using the operators <literal>UNION</literal>,
88 <literal>INTERSECT</literal>, and <literal>EXCEPT</literal>, the
89 output of more than one <command>SELECT</command> statement can
90 be combined to form a single result set. The
91 <literal>UNION</literal> operator returns all rows that are in
92 one or both of the result sets. The
93 <literal>INTERSECT</literal> operator returns all rows that are
94 strictly in both result sets. The <literal>EXCEPT</literal>
95 operator returns the rows that are in the first result set but
96 not in the second. In all three cases, duplicate rows are
97 eliminated unless <literal>ALL</literal> is specified. (See
98 <xref linkend="sql-union" endterm="sql-union-title">, <xref
99 linkend="sql-intersect" endterm="sql-intersect-title">, and
100 <xref linkend="sql-except" endterm="sql-except-title"> below.)
106 The actual output rows are computed the
107 <command>SELECT</command> output expressions for each selected
109 <xref linkend="sql-select-list" endterm="sql-select-list-title">
116 If the <literal>ORDER BY</literal> clause is specified, the
117 returned rows are sorted in the specified order. If
118 <literal>ORDER BY</literal> is not given, the rows are returned
119 in whatever order the system finds fastest to produce. (See
120 <xref linkend="sql-orderby" endterm="sql-orderby-title"> below.)
126 If the <literal>LIMIT</literal> or <literal>OFFSET</literal>
127 clause is specified, the <command>SELECT</command> statement
128 only returns a subset of the result rows. (See <xref
129 linkend="sql-limit" endterm="sql-limit-title"> below.)
135 <literal>DISTINCT</literal> eliminates duplicate rows from the
136 result. <literal>DISTINCT ON</literal> eliminates rows that
137 match on all the specified expressions. <literal>ALL</literal>
138 (the default) will return all candidate rows, including
139 duplicates. (See <xref linkend="sql-distinct"
140 endterm="sql-distinct-title"> below.)
146 The <literal>FOR UPDATE</literal> clause causes the
147 <command>SELECT</command> statement to lock the selected rows
148 against concurrent updates. (See <xref linkend="sql-for-update"
149 endterm="sql-for-update-title"> below.)
156 You must have <literal>SELECT</literal> privilege on a table to
157 read its values. The use of <literal>FOR UPDATE</literal> requires
158 <literal>UPDATE</literal> privilege as well.
163 <title>Parameters</title>
165 <refsect2 id="SQL-FROM">
166 <title id="sql-from-title"><literal>FROM</literal> Clause</title>
169 The <literal>FROM</literal> clause specifies one or more source
170 tables for the <command>SELECT</command>. If multiple sources are
171 specified, the result is the Cartesian product (cross join) of all
172 the sources. But usually qualification conditions
173 are added to restrict the returned rows to a small subset of the
178 <literal>FROM</literal>-clause elements can contain:
182 <term><replaceable class="parameter">table_name</replaceable></term>
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
194 <varname>sql_interitance</varname> configuration option.
200 <term><replaceable class="parameter">alias</replaceable></term>
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.
218 <term><replaceable class="parameter">select</replaceable></term>
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
233 <term><replaceable class="parameter">function_name</replaceable></term>
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.
257 <term><replaceable class="parameter">join_type</replaceable></term>
263 <para><literal>[ INNER ] JOIN</literal></para>
266 <para><literal>LEFT [ OUTER ] JOIN</literal></para>
269 <para><literal>RIGHT [ OUTER ] JOIN</literal></para>
272 <para><literal>FULL [ OUTER ] JOIN</literal></para>
275 <para><literal>CROSS JOIN</literal></para>
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.
290 A <literal>JOIN</literal> clause, combines two
291 <literal>FROM</> items. (Use parentheses if necessary to
292 determine the order of nesting.)
296 <literal>CROSS JOIN</> and <literal>INNER JOIN</literal>
297 produce a simple Cartesian product, the same as you get from
298 listing the two items at the top level of <literal>FROM</>.
299 <literal>CROSS JOIN</> is equivalent to <literal>INNER JOIN ON
300 (true)</>, that is, no rows are removed by qualification.
301 These join types are just a notational convenience, since they
302 do nothing you couldn't do with plain <literal>FROM</> and
307 <literal>LEFT OUTER JOIN</> returns all rows in the qualified
308 Cartesian product (i.e., all combined rows that pass its join
309 condition), plus one copy of each row in the left-hand table
310 for which there was no right-hand row that passed the join
311 condition. This left-hand row is extended to the full width
312 of the joined table by inserting null values for the
313 right-hand columns. Note that only the <literal>JOIN</>
314 clauses own condition is considered while deciding which rows
315 have matches. Outer conditions are applied afterwards.
319 Conversely, <literal>RIGHT OUTER JOIN</> returns all the
320 joined rows, plus one row for each unmatched right-hand row
321 (extended with nulls on the left). This is just a notational
322 convenience, since you could convert it to a <literal>LEFT
323 OUTER JOIN</> by switching the left and right inputs.
327 <literal>FULL OUTER JOIN</> returns all the joined rows, plus
328 one row for each unmatched left-hand row (extended with nulls
329 on the right), plus one row for each unmatched right-hand row
330 (extended with nulls on the left).
336 <term><literal>ON <replaceable class="parameter">join_condition</replaceable></literal></term>
339 <replaceable class="parameter">join_condition</replaceable> is
340 an expression resulting in a value of type
341 <type>boolean</type> (similar to a <literal>WHERE</literal>
342 clause) that specifies which rows in a join are considered to
349 <term><literal>USING (<replaceable class="parameter">join_column</replaceable> [, ...])</literal></term>
352 A clause of the form <literal>USING ( a, b, ... )</literal> is
353 shorthand for <literal>ON left_table.a = right_table.a AND
354 left_table.b = right_table.b ...</literal>. Also,
355 <literal>USING</> implies that only one of each pair of
356 equivalent columns will be included in the join output, not
363 <term><literal>NATURAL</literal></term>
366 <literal>NATURAL</literal> is shorthand for a
367 <literal>USING</> list that mentions all columns in the two
368 tables that have the same names.
376 <refsect2 id="SQL-WHERE">
377 <title id="sql-where-title"><literal>WHERE</literal> Clause</title>
380 The optional <literal>WHERE</literal> clause has the general form
382 WHERE <replaceable class="parameter">condition</replaceable>
384 where <replaceable class="parameter">condition</replaceable> is
385 any expression that evaluates to a result of type
386 <type>boolean</type>. Any row that does not satisfy this
387 condition will be eliminated from the output. A row satisfies the
388 condition if it returns true when the actual row values are
389 substituted for any variable references.
393 <refsect2 id="SQL-GROUPBY">
394 <title id="sql-groupby-title"><literal>GROUP BY</literal> Clause</title>
397 The optional <literal>GROUP BY</literal> clause has the general form
399 GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
404 <literal>GROUP BY</literal> will condense into a single row all
405 selected rows that share the same values for the grouped
406 expressions. <replaceable
407 class="parameter">expression</replaceable> can be an input column
408 name, or the name or ordinal number of an output column
409 (<command>SELECT</command> list), or it can be an arbitrary
410 expression formed from input-column values. In case of ambiguity,
411 a <literal>GROUP BY</literal> name will be interpreted as an
412 input-column name rather than an output column name.
416 Aggregate functions, if any are used, are computed across all rows
417 making up each group, producing a separate value for each group
418 (whereas without <literal>GROUP BY</literal>, an aggregate
419 produces a single value computed across all the selected rows).
420 When <literal>GROUP BY</literal> is present, it is not valid for
421 the <command>SELECT</command> list expressions to refer to
422 ungrouped columns except within aggregate functions, since there
423 would be more than one possible value to return for an ungrouped
428 <refsect2 id="SQL-HAVING">
429 <title id="sql-having-title"><literal>HAVING</literal> Clause</title>
432 The optional <literal>HAVING</literal> clause has the general form
434 HAVING <replaceable class="parameter">condition</replaceable>
436 where <replaceable class="parameter">condition</replaceable> is
437 the same as specified for the <literal>WHERE</literal> clause.
441 <literal>HAVING</literal> eliminates group rows that do not
442 satisfy the condition. <literal>HAVING</literal> is different
443 from <literal>WHERE</literal>: <literal>WHERE</literal> filters
444 individual rows before the application of <literal>GROUP
445 BY</literal>, while <literal>HAVING</literal> filters group rows
446 created by <literal>GROUP BY</literal>. Each column referenced in
447 <replaceable class="parameter">condition</replaceable> must
448 unambiguously reference a grouping column, unless the reference
449 appears within an aggregate function.
453 <refsect2 id="SQL-UNION">
454 <title id="sql-union-title"><literal>UNION</literal> Clause</title>
457 The <literal>UNION</literal> clause has this general form:
459 <replaceable class="parameter">select_statement</replaceable> UNION [ ALL ] <replaceable class="parameter">select_statement</replaceable>
461 <replaceable class="parameter">select_statement</replaceable> is
462 any <command>SELECT</command> statement without an <literal>ORDER
463 BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
464 (<literal>ORDER BY</> and <literal>LIMIT</> can be attached to a
465 subexpression if it is enclosed in parentheses. Without
466 parentheses, these clauses will be taken to apply to the result of
467 the <literal>UNION</literal>, not to its right-hand input
472 The <literal>UNION</literal> operator computes the set union of
473 the rows returned by the involved <command>SELECT</command>
474 statements. A row is in the set union of two result sets if it
475 appears in at least one of the result sets. The two
476 <command>SELECT</command> statements that represent the direct
477 operands of the <literal>UNION</literal> must produce the same
478 number of columns, and corresponding columns must be of compatible
483 The result of <literal>UNION</> does not contain any duplicate
484 rows unless the <literal>ALL</> option is specified.
485 <literal>ALL</> prevents elimination of duplicates.
489 Multiple <literal>UNION</> operators in the same
490 <command>SELECT</command> statement are evaluated left to right,
491 unless otherwise indicated by parentheses.
495 Currently, <literal>FOR UPDATE</> may not be specified either for
496 a <literal>UNION</> result or for the inputs of <literal>UNION</>.
500 <refsect2 id="SQL-INTERSECT">
501 <title id="sql-intersect-title"><literal>INTERSECT</literal> Clause</title>
504 The <literal>INTERSECT</literal> clause has this general form:
506 <replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
508 <replaceable class="parameter">select_statement</replaceable> is
509 any <command>SELECT</command> statement without an <literal>ORDER
510 BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
514 The <literal>INTERSECT</literal> operator computes the set
515 intersection of the rows returned by the involved
516 <command>SELECT</command> statements. A row is in the
517 intersection of two result sets if it appears in both result sets.
521 The result of <literal>INTERSECT</literal> does not contain any
522 duplicate rows unless the <literal>ALL</> option is specified.
523 With <literal>ALL</>, a row that has m duplicates in the left
524 table and n duplicates in the right table will appear min(m,n)
525 times in the result set.
529 Multiple <literal>INTERSECT</literal> operators in the same
530 <command>SELECT</command> statement are evaluated left to right,
531 unless parentheses dictate otherwise.
532 <literal>INTERSECT</literal> binds more tightly than
533 <literal>UNION</literal>. That is, <literal>A UNION B INTERSECT
534 C</literal> will be read as <literal>A UNION (B INTERSECT
539 <refsect2 id="SQL-EXCEPT">
540 <title id="sql-except-title"><literal>EXCEPT</literal> Clause</title>
543 The <literal>EXCEPT</literal> clause has this general form:
545 <replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
547 <replaceable class="parameter">select_statement</replaceable> is
548 any <command>SELECT</command> statement without an <literal>ORDER
549 BY</>, <literal>LIMIT</>, or <literal>FOR UPDATE</literal> clause.
553 The <literal>EXCEPT</literal> operator computes the set of rows
554 that are in the result of the left <command>SELECT</command>
555 statement but not in the result of the right one.
559 The result of <literal>EXCEPT</literal> does not contain any
560 duplicate rows unless the <literal>ALL</> option is specified.
561 With <literal>ALL</>, a row that has m duplicates in the left
562 table and n duplicates in the right table will appear max(m-n,0)
563 times in the result set.
567 Multiple <literal>EXCEPT</literal> operators in the same
568 <command>SELECT</command> statement are evaluated left to right,
569 unless parentheses dictate otherwise. <literal>EXCEPT</> binds at
570 the same level as <literal>UNION</>.
574 <refsect2 id="sql-select-list">
575 <title id="sql-select-list-title"><command>SELECT</command> List</title>
578 The <command>SELECT</command> list (between the key words
579 <literal>SELECT</> and <literal>FROM</>) specifies expressions
580 that form the output rows of the <command>SELECT</command>
581 statement. The expressions can (and usually do) refer to columns
582 computed in the <literal>FROM</> clause. Using the clause
583 <literal>AS <replaceable
584 class="parameter">output_name</replaceable></literal>, another
585 name can be specified for an output column. This name is
586 primarily used to label the column for display. It can also be
587 used to refer to the column's value in <literal>ORDER BY</> and
588 <literal>GROUP BY</> clauses, but not in the <literal>WHERE</> or
589 <literal>HAVING</> clauses; there you must write out the
594 Instead of an expression, <literal>*</literal> can be written in
595 the output list as a shorthand for all the columns of the selected
596 rows. Also, one can write <literal><replaceable
597 class="parameter">table_name</replaceable>.*</literal> as a
598 shorthand for the columns coming from just that table.
602 <refsect2 id="SQL-ORDERBY">
603 <title id="sql-orderby-title"><literal>ORDER BY</literal> Clause</title>
606 The optional <literal>ORDER BY</literal> clause has this general form:
608 ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...]
610 <replaceable class="parameter">expression</replaceable> can be the
611 name or ordinal number of an output column
612 (<command>SELECT</command> list), or it can be an arbitrary
613 expression formed from input-column values.
617 The <literal>ORDER BY</literal> clause causes the result rows to
618 be sorted according to the specified expressions. If two rows are
619 equal according to the leftmost expression, the are compared
620 according to the next expression and so on. If they are equal
621 according to all specified expressions, they are returned in
626 The ordinal number refers to the ordinal (left-to-right) position
627 of the result column. This feature makes it possible to define an
628 ordering on the basis of a column that does not have a unique
629 name. This is never absolutely necessary because it is always
630 possible to assign a name to a result column using the
631 <literal>AS</> clause.
635 It is also possible to use arbitrary expressions in the
636 <literal>ORDER BY</literal> clause, including columns that do not
637 appear in the <command>SELECT</command> result list. Thus the
638 following statement is valid:
640 SELECT name FROM distributors ORDER BY code;
642 A limitation of this feature is that an <literal>ORDER BY</>
643 clause applying to the result of a <literal>UNION</>,
644 <literal>INTERSECT</>, or <literal>EXCEPT</> clause may only
645 specify an output column name or number, not an expression.
649 If an <literal>ORDER BY</> expression is a simple name that
650 matches both a result column name and an input column name,
651 <literal>ORDER BY</> will interpret it as the result column name.
652 This is the opposite of the choice that <literal>GROUP BY</> will
653 make in the same situation. This inconsistency is made to be
654 compatible with the SQL standard.
658 Optionally one may add the key word <literal>ASC</> (ascending) or
659 <literal>DESC</> (descending) after each expression in the
660 <literal>ORDER BY</> clause. If not specified, <literal>ASC</> is
661 assumed by default. Alternatively, a specific ordering operator
662 name may be specified in the <literal>USING</> clause.
663 <literal>ASC</> is usually equivalent to <literal>USING <</> and
664 <literal>DESC</> is usually equivalent to <literal>USING ></>.
665 (But the creator of a user-defined datatype can define exactly what the
666 default sort ordering is, and it might correspond to operators with other
671 The null value sorts higher than any other value. In other words,
672 with ascending sort order, null values sort at the end, and with
673 descending sort order, null values sort at the beginning.
677 Character-string data is sorted according to the locale-specific
678 collation order that was established when the database cluster
683 <refsect2 id="SQL-LIMIT">
684 <title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
687 The <literal>LIMIT</literal> clause consists of two independent
690 LIMIT { <replaceable class="parameter">count</replaceable> | ALL }
691 OFFSET <replaceable class="parameter">start</replaceable>
693 <replaceable class="parameter">count</replaceable> specifies the
694 maximum number of rows to return, and <replaceable
695 class="parameter">start</replaceable> specifies the number of rows
696 to skip before starting to return rows.
700 When using <literal>LIMIT</>, it is a good idea to use an
701 <literal>ORDER BY</> clause that constrains the result rows into a
702 unique order. Otherwise you will get an unpredictable subset of
703 the query's rows---you may be asking for the tenth through
704 twentieth rows, but tenth through twentieth in what ordering? You
705 don't know what ordering unless you specify <literal>ORDER BY</>.
709 The query planner takes <literal>LIMIT</> into account when
710 generating a query plan, so you are very likely to get different
711 plans (yielding different row orders) depending on what you use
712 for <literal>LIMIT</> and <literal>OFFSET</>. Thus, using
713 different <literal>LIMIT</>/<literal>OFFSET</> values to select
714 different subsets of a query result <emphasis>will give
715 inconsistent results</emphasis> unless you enforce a predictable
716 result ordering with <literal>ORDER BY</>. This is not a bug; it
717 is an inherent consequence of the fact that SQL does not promise
718 to deliver the results of a query in any particular order unless
719 <literal>ORDER BY</> is used to constrain the order.
723 <refsect2 id="sql-distinct">
724 <title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
727 If <literal>DISTINCT</> is specified, all duplicate rows are
728 removed from the result set (one row is kept from each group of
729 duplicates). <literal>ALL</> specifies the opposite: all rows are
730 kept; that is the default.
734 <literal>DISTINCT ON ( <replaceable
735 class="parameter">expression</replaceable> [, ...] )</literal>
736 keeps only the first row of each set of rows where the given
737 expressions evaluate to equal. The <literal>DISTINCT ON</literal>
738 expressions are interpreted using the same rules as for
739 <literal>ORDER BY</> (see above). Note that the <quote>first
740 row</quote> of each set is unpredictable unless <literal>ORDER
741 BY</> is used to ensure that the desired row appears first. For
744 SELECT DISTINCT ON (location) location, time, report
746 ORDER BY location, time DESC;
748 retrieves the most recent weather report for each location. But
749 if we had not used <literal>ORDER BY</> to force descending order
750 of time values for each location, we'd have gotten a report from
751 an unpredictable time for each location.
755 <refsect2 id="SQL-FOR-UPDATE">
756 <title id="sql-for-update-title"><literal>FOR UPDATE</literal> Clause</title>
759 The <literal>FOR UPDATE</literal> clause has this form:
761 FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
766 <literal>FOR UPDATE</literal> causes the rows retrieved by the
767 <command>SELECT</command> statement to be locked as though for
768 update. This prevents them from being modified or deleted by
769 other transactions until the current transaction ends. That is,
770 other transactions that attempt <command>UPDATE</command>,
771 <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
772 of these rows will be blocked until the current transaction ends.
773 Also, if an <command>UPDATE</command>, <command>DELETE</command>,
774 or <command>SELECT FOR UPDATE</command> from another transaction
775 has already locked a selected row or rows, <command>SELECT FOR
776 UPDATE</command> will wait for the other transaction to complete,
777 and will then lock and return the updated row (or no row, if the
778 row was deleted). For further discussion see <xref
783 If specific tables are named in <literal>FOR UPDATE</literal>,
784 then only rows coming from those tables are locked; any other
785 tables used in the <command>SELECT</command> are simply read as
790 <literal>FOR UPDATE</literal> cannot be used in contexts where
791 returned rows can't be clearly identified with individual table
792 rows; for example it can't be used with aggregation.
796 <literal>FOR UPDATE</literal> may appear before
797 <literal>LIMIT</literal> for compatibility with PostgreSQL
798 versions before 7.3. It effectively executes after
799 <literal>LIMIT</literal>, however, and so that is the recommended
806 <title>Examples</title>
809 To join the table <literal>films</literal> with the table
810 <literal>distributors</literal>:
813 SELECT f.title, f.did, d.name, f.date_prod, f.kind
814 FROM distributors d, films f
817 title | did | name | date_prod | kind
818 -------------------+-----+--------------+------------+----------
819 The Third Man | 101 | British Lion | 1949-12-23 | Drama
820 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
826 To sum the column <literal>len</literal> of all films and group
827 the results by <literal>kind</literal>:
830 SELECT kind, sum(len) AS total FROM films GROUP BY kind;
843 To sum the column <literal>len</literal> of all films, group
844 the results by <literal>kind</literal> and show those group totals
845 that are less than 5 hours:
848 SELECT kind, sum(len) AS total
851 HAVING sum(len) < interval '5 hours';
861 The following two examples are identical ways of sorting the individual
862 results according to the contents of the second column
863 (<literal>name</literal>):
866 SELECT * FROM distributors ORDER BY name;
867 SELECT * FROM distributors ORDER BY 2;
870 -----+------------------
871 109 | 20th Century Fox
872 110 | Bavaria Atelier
875 102 | Jean Luc Godard
888 This example shows how to obtain the union of the tables
889 <literal>distributors</literal> and
890 <literal>actors</literal>, restricting the results to those that begin
891 with letter W in each table. Only distinct rows are wanted, so the
892 key word <literal>ALL</literal> is omitted.
895 distributors: actors:
897 -----+-------------- ----+----------------
898 108 | Westward 1 | Woody Allen
899 111 | Walt Disney 2 | Warren Beatty
900 112 | Warner Bros. 3 | Walter Matthau
903 SELECT distributors.name
905 WHERE distributors.name LIKE 'W%'
909 WHERE actors.name LIKE 'W%';
923 This example shows how to use a function in the <literal>FROM</>
924 clause, both with and without a column definition list.
927 CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS '
928 SELECT * FROM distributors WHERE did = $1;
931 SELECT * FROM distributors(111);
936 CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS '
937 SELECT * FROM distributors WHERE did = $1;
940 SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
949 <title>Compatibility</title>
952 Of course, the <command>SELECT</command> statement is compatible
953 with the SQL standard. But there are some extensions and some
958 <title>Omitted <literal>FROM</literal> Clauses</title>
961 <productname>PostgreSQL</productname> allows one to omit the
962 <literal>FROM</literal> clause. It has a straightforward use to
963 compute the results of simple expressions:
971 Some other <acronym>SQL</acronym> databases cannot do this except
972 by introducing a dummy one-row table from which to do the
973 <command>SELECT</command>.
977 A less obvious use is to abbreviate a normal
978 <command>SELECT</command> from tables:
980 SELECT distributors.* WHERE distributors.name = 'Westward';
986 This works because an implicit <literal>FROM</literal> item is
987 added for each table that is referenced in other parts of the
988 <command>SELECT</command> statement but not mentioned in
989 <literal>FROM</literal>.
993 While this is a convenient shorthand, it's easy to misuse. For
996 SELECT distributors.* FROM distributors d;
998 is probably a mistake; most likely the user meant
1000 SELECT d.* FROM distributors d;
1002 rather than the unconstrained join
1004 SELECT distributors.* FROM distributors d, distributors distributors;
1006 that he will actually get. To help detect this sort of mistake,
1007 PostgreSQL will warn if the implicit-<literal>FROM</literal>
1008 feature is used in a <command>SELECT</command> statement that also
1009 contains an explicit <literal>FROM</literal> clause. Also, it is
1010 possible to disable the implicit-<literal>FROM</literal> feature
1011 by setting the <varname>ADD_MISSING_FROM</> parameter to false.
1016 <title>The <literal>AS</literal> Key Word</title>
1019 In the SQL standard, the optional key word <literal>AS</> is just
1020 noise and can be omitted without affecting the meaning. The
1021 <productname>PostgreSQL</productname> parser requires this key
1022 word when renaming output columns because the type extensibility
1023 features lead to parsing ambiguities without it.
1024 <literal>AS</literal> is optional in <literal>FROM</literal>
1030 <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
1033 In the SQL92 standard, an <literal>ORDER BY</literal> clause may
1034 only use result column names or numbers, while a <literal>GROUP
1035 BY</literal> clause may only use expressions based on input column
1036 names. <productname>PostgreSQL</productname> extends each of
1037 these clauses to allow the other choice as well (but it uses the
1038 standard's interpretation if there is ambiguity).
1039 <productname>PostgreSQL</productname> also allows both clauses to
1040 specify arbitrary expressions. Note that names appearing in an
1041 expression will always be taken as input-column names, not as
1042 result-column names.
1046 SQL99 uses a slightly different definition which is not upward compatible
1047 with SQL92. In most cases, however, <productname>PostgreSQL</productname>
1048 will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1049 BY</literal> expression the same way SQL99 does.
1054 <title>Nonstandard Clauses</title>
1057 The clauses <literal>DISTINCT ON</literal>,
1058 <literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
1059 defined in the SQL standard.
1065 <!-- Keep this comment at the end of the file
1070 sgml-minimize-attributes:nil
1071 sgml-always-quote-attributes:t
1074 sgml-parent-document:nil
1075 sgml-default-dtd-file:"../reference.ced"
1076 sgml-exposed-tags:nil
1077 sgml-local-catalogs:"/usr/lib/sgml/catalog"
1078 sgml-local-ecat-files:nil