2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.29 2000/06/09 01:44:00 momjian Exp $
6 <refentry id="SQL-SELECT">
8 <refentrytitle id="sql-select-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Retrieve rows from a table or view.
19 </refpurpose></refnamediv>
22 <date>1999-07-20</date>
25 SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
26 <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ] [, ...]
27 [ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
28 [ FROM [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ <replaceable class="PARAMETER">alias</replaceable> ] [, ...] ]
29 [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
30 [ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]
31 [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
32 [ { UNION [ ALL ] | INTERSECT | EXCEPT } <replaceable class="PARAMETER">select</replaceable> ]
33 [ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
34 [ FOR UPDATE [ OF <replaceable class="PARAMETER">class_name</replaceable> [, ...] ] ]
35 LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } [ { OFFSET | , } <replaceable class="PARAMETER">start</replaceable> ]
38 <refsect2 id="R2-SQL-SELECT-1">
40 <date>2000-03-15</date>
49 <term><replaceable class="PARAMETER">expression</replaceable></term>
52 The name of a table's column or an expression.
58 <term><replaceable class="PARAMETER">name</replaceable></term>
61 Specifies another name for a column or an expression using
62 the AS clause. This name is primarily used to label the column
63 for display. It can also be used to refer to the column's value in
64 ORDER BY and GROUP BY clauses. But the
65 <replaceable class="PARAMETER">name</replaceable>
66 cannot be used in the WHERE or HAVING clauses; write out the
73 <term>TEMPORARY</term>
77 If TEMPORARY or TEMP is specified,
78 the table is created unique to this session, and is
79 automatically dropped on session exit.
85 <term><replaceable class="PARAMETER">new_table</replaceable></term>
88 If the INTO TABLE clause is specified, the result of the
89 query will be stored in a new table with the indicated
91 The target table (<replaceable class="PARAMETER">new_table</replaceable>) will
92 be created automatically and must not exist before this command.
93 Refer to <command>SELECT INTO</command> for more information.
97 The <command>CREATE TABLE AS</command> statement will also
98 create a new table from a select query.
106 <term><replaceable class="PARAMETER">table</replaceable></term>
109 The name of an existing table referenced by the FROM clause.
115 <term><replaceable class="PARAMETER">alias</replaceable></term>
118 An alternate name for the preceding
119 <replaceable class="PARAMETER">table</replaceable>.
120 It is used for brevity or to eliminate ambiguity for joins
121 within a single table.
127 <term><replaceable class="PARAMETER">condition</replaceable></term>
130 A boolean expression giving a result of true or false.
131 See the WHERE clause.
137 <term><replaceable class="PARAMETER">column</replaceable></term>
140 The name of a table's column.
146 <term><replaceable class="PARAMETER">select</replaceable></term>
149 A select statement with all features except the ORDER BY and
159 <refsect2 id="R2-SQL-SELECT-2">
161 <date>1998-09-24</date>
173 The complete set of rows resulting from the query specification.
180 <returnvalue><replaceable>count</replaceable></returnvalue>
184 The count of rows returned by the query.
193 <refsect1 id="R1-SQL-SELECT-1">
195 <date>2000-03-15</date>
201 <command>SELECT</command> will return rows from one or more tables.
202 Candidates for selection are rows which satisfy the WHERE condition;
203 if WHERE is omitted, all rows are candidates.
204 (See <xref linkend="sql-where" endterm="sql-where-title">.)
207 <command>ONLY</command> will eliminate rows from subclasses of the table.
208 This was previously the default result, and getting subclasses was
209 obtained by appending <command>*</command> to the table name.
210 The old behaviour is available via the command
211 <command>SET EXAMINE_SUBCLASS TO 'on';</command>
215 <command>DISTINCT</command> will eliminate duplicate rows from the
217 <command>ALL</command> (the default) will return all candidate rows,
218 including duplicates.
222 <command>DISTINCT ON</command> eliminates rows that match on all the
223 specified expressions, keeping only the first row of each set of
224 duplicates. The DISTINCT ON expressions are interpreted using the
225 same rules as for ORDER BY items; see below.
226 Note that "the first row" of each set is unpredictable
227 unless <command>ORDER BY</command> is used to ensure that the desired
228 row appears first. For example,
230 SELECT DISTINCT ON (location) location, time, report
232 ORDER BY location, time DESC;
234 retrieves the most recent weather report for each location. But if
235 we had not used ORDER BY to force descending order of time values
236 for each location, we'd have gotten a report of unpredictable age
241 The GROUP BY clause allows a user to divide a table
242 into groups of rows that match on one or more values.
243 (See <xref linkend="sql-groupby" endterm="sql-groupby-title">.)
247 The HAVING clause allows selection of only those groups of rows
248 meeting the specified condition.
249 (See <xref linkend="sql-having" endterm="sql-having-title">.)
253 The ORDER BY clause causes the returned rows to be sorted in a specified
254 order. If ORDER BY is not given, the rows are returned in whatever order
255 the system finds cheapest to produce.
256 (See <xref linkend="sql-orderby-title" endterm="sql-orderby-title">.)
260 The UNION operator allows the result to be the collection of rows
261 returned by the queries involved.
262 (See <xref linkend="sql-union" endterm="sql-union-title">.)
266 The INTERSECT operator gives you the rows that are common to both queries.
267 (See <xref linkend="sql-intersect" endterm="sql-intersect-title">.)
271 The EXCEPT operator gives you the rows returned by the first query but
272 not the second query.
273 (See <xref linkend="sql-except" endterm="sql-except-title">.)
277 The FOR UPDATE clause allows the SELECT statement to perform
278 exclusive locking of selected rows.
282 The LIMIT clause allows a subset of the rows produced by the query
283 to be returned to the user.
284 (See <xref linkend="sql-limit" endterm="sql-limit-title">.)
288 You must have SELECT privilege to a table to read its values
289 (See the <command>GRANT</command>/<command>REVOKE</command> statements).
292 <refsect2 id="SQL-WHERE">
294 <date>2000-03-15</date>
296 <title id="sql-where-title">
301 The optional WHERE condition has the general form:
304 WHERE <replaceable class="PARAMETER">boolean_expr</replaceable>
307 <replaceable class="PARAMETER">boolean_expr</replaceable>
308 can consist of any expression which evaluates to a boolean value.
309 In many cases, this expression will be
312 <replaceable class="PARAMETER">expr</replaceable> <replaceable class="PARAMETER">cond_op</replaceable> <replaceable class="PARAMETER">expr</replaceable>
318 <replaceable class="PARAMETER">log_op</replaceable> <replaceable class="PARAMETER">expr</replaceable>
321 where <replaceable class="PARAMETER">cond_op</replaceable>
322 can be one of: =, <, <=, >, >= or <>,
323 a conditional operator like ALL, ANY, IN, LIKE, or a
324 locally-defined operator,
325 and <replaceable class="PARAMETER">log_op</replaceable> can be one
327 SELECT will ignore all rows for which the WHERE condition does not return
332 <refsect2 id="SQL-GROUPBY">
334 <date>2000-03-15</date>
336 <title id="sql-groupby-title">
340 GROUP BY specifies a grouped table derived by the application
343 GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...]
348 GROUP BY will condense into a single row all selected rows that share the
349 same values for the grouped columns. Aggregate functions, if any,
350 are computed across all rows making up each group, producing a
351 separate value for each group (whereas without GROUP BY, an
352 aggregate produces a single value computed across all the selected
353 rows). When GROUP BY is present, it is not valid for the SELECT
354 output expression(s) to refer to
355 ungrouped columns except within aggregate functions, since there
356 would be more than one possible value to return for an ungrouped column.
360 An item in GROUP BY can also be the name or ordinal number of an output
361 column (SELECT expression), or it can be an arbitrary expression formed
362 from input-column values. In case of ambiguity, a GROUP BY name will
363 be interpreted as an input-column name rather than an output column name.
367 <refsect2 id="SQL-HAVING">
369 <date>2000-03-15</date>
371 <title id="sql-having-title">
375 The optional HAVING condition has the general form:
378 HAVING <replaceable class="PARAMETER">cond_expr</replaceable>
381 where <replaceable class="PARAMETER">cond_expr</replaceable> is the same
382 as specified for the WHERE clause.
386 HAVING specifies a grouped table derived by the elimination
387 of group rows that do not satisfy the
388 <replaceable class="PARAMETER">cond_expr</replaceable>.
389 HAVING is different from WHERE:
390 WHERE filters individual rows before application of GROUP BY,
391 while HAVING filters group rows created by GROUP BY.
395 Each column referenced in
396 <replaceable class="PARAMETER">cond_expr</replaceable> shall unambiguously
397 reference a grouping column, unless the reference appears within an
402 <refsect2 id="SQL-ORDERBY">
404 <date>2000-03-15</date>
406 <title id="sql-orderby-title">
411 ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...]
415 <replaceable class="PARAMETER">column</replaceable> can be either a
416 result column name or an ordinal number.
419 The ordinal numbers refers to the ordinal (left-to-right) position
420 of the result column. This feature makes it possible to define an ordering
421 on the basis of a column that does not have a proper name.
422 This is never absolutely necessary because it is always possible
423 to assign a name to a result column using the AS clause, e.g.:
425 SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;
426 </programlisting></para>
429 It is also possible to ORDER BY
430 arbitrary expressions (an extension to SQL92),
431 including fields that do not appear in the
433 Thus the following statement is legal:
435 SELECT name FROM distributors ORDER BY code;
438 Note that if an ORDER BY item is a simple name that matches both
439 a result column name and an input column name, ORDER BY will interpret
440 it as the result column name. This is the opposite of the choice that
441 GROUP BY will make in the same situation. This inconsistency is
442 mandated by the SQL92 standard.
446 Optionally one may add the keyword DESC (descending)
447 or ASC (ascending) after each column name in the ORDER BY clause.
448 If not specified, ASC is assumed by default. Alternatively, a
449 specific ordering operator name may be specified. ASC is equivalent
450 to USING '<' and DESC is equivalent to USING '>'.
454 <refsect2 id="SQL-UNION">
456 <date>1998-09-24</date>
458 <title id="sql-union-title">
463 <replaceable class="PARAMETER">table_query</replaceable> UNION [ ALL ] <replaceable class="PARAMETER">table_query</replaceable>
464 [ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...] ]
468 <replaceable class="PARAMETER">table_query</replaceable>
469 specifies any select expression without an ORDER BY or LIMIT clause.
473 The UNION operator allows the result to be the collection of rows
474 returned by the queries involved.
475 The two SELECTs that represent the direct operands of the UNION must
476 produce the same number of columns, and corresponding columns must be
477 of compatible data types.
481 By default, the result of UNION does not contain any duplicate rows
482 unless the ALL clause is specified.
486 Multiple UNION operators in the same SELECT statement are
487 evaluated left to right.
488 Note that the ALL keyword is not global in nature, being
489 applied only for the current pair of table results.
494 <refsect2 id="SQL-INTERSECT">
496 <date>1998-09-24</date>
498 <title id="sql-intersect-title">
503 <replaceable class="PARAMETER">table_query</replaceable> INTERSECT <replaceable class="PARAMETER">table_query</replaceable>
504 [ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...] ]
508 <replaceable class="PARAMETER">table_query</replaceable>
509 specifies any select expression without an ORDER BY or LIMIT clause.
513 The INTERSECT operator gives you the rows that are common to both queries.
514 The two SELECTs that represent the direct operands of the INTERSECT must
515 produce the same number of columns, and corresponding columns must be
516 of compatible data types.
520 Multiple INTERSECT operators in the same SELECT statement are
521 evaluated left to right, unless parentheses dictate otherwise.
525 <refsect2 id="SQL-EXCEPT">
527 <date>1998-09-24</date>
529 <title id="sql-except-title">
534 <replaceable class="PARAMETER">table_query</replaceable> EXCEPT <replaceable class="PARAMETER">table_query</replaceable>
535 [ ORDER BY <replaceable class="PARAMETER">column</replaceable> [ ASC | DESC ] [, ...] ]
539 <replaceable class="PARAMETER">table_query</replaceable>
540 specifies any select expression without an ORDER BY or LIMIT clause.
544 The EXCEPT operator gives you the rows returned by the first query but
545 not the second query.
546 The two SELECTs that represent the direct operands of the EXCEPT must
547 produce the same number of columns, and corresponding columns must be
548 of compatible data types.
552 Multiple EXCEPT operators in the same SELECT statement are
553 evaluated left to right, unless parentheses dictate otherwise.
557 <refsect2 id="SQL-LIMIT">
559 <date>2000-02-20</date>
561 <title id="sql-limit-title">
566 LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } [ { OFFSET | , } <replaceable class="PARAMETER">start</replaceable> ]
567 OFFSET <replaceable class="PARAMETER">start</replaceable>
571 <replaceable class="PARAMETER">count</replaceable> specifies the
572 maximum number of rows to return, and
573 <replaceable class="PARAMETER">start</replaceable> specifies the
574 number of rows to skip before starting to return rows.
578 LIMIT allows you to retrieve just a portion of the rows that are generated
579 by the rest of the query. If a limit count is given, no more than that
580 many rows will be returned. If an offset is given, that many rows will
581 be skipped before starting to return rows.
585 When using LIMIT, it is a good idea to use an ORDER BY clause that
586 constrains the result rows into a unique order. Otherwise you will get
587 an unpredictable subset of the query's rows --- you may be asking for
588 the tenth through twentieth rows, but tenth through twentieth in what
589 ordering? You don't know what ordering, unless you specified ORDER BY.
593 As of <productname>Postgres</productname> 7.0, the
594 query optimizer takes LIMIT into account when generating a query plan,
595 so you are very likely to get different plans (yielding different row
596 orders) depending on what you give for LIMIT and OFFSET. Thus, using
597 different LIMIT/OFFSET values to select different subsets of a query
598 result <emphasis>will give inconsistent results</emphasis> unless
599 you enforce a predictable result ordering with ORDER BY. This is not
600 a bug; it is an inherent consequence of the fact that SQL does not
601 promise to deliver the results of a query in any particular order
602 unless ORDER BY is used to constrain the order.
607 <refsect1 id="R1-SQL-SELECT-2">
613 To join the table <literal>films</literal> with the table
614 <literal>distributors</literal>:
617 SELECT f.title, f.did, d.name, f.date_prod, f.kind
618 FROM distributors d, films f
621 title | did | name | date_prod | kind
622 ---------------------------+-----+------------------+------------+----------
623 The Third Man | 101 | British Lion | 1949-12-23 | Drama
624 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
625 Une Femme est une Femme | 102 | Jean Luc Godard | 1961-03-12 | Romantic
626 Vertigo | 103 | Paramount | 1958-11-14 | Action
627 Becket | 103 | Paramount | 1964-02-03 | Drama
628 48 Hrs | 103 | Paramount | 1982-10-22 | Action
629 War and Peace | 104 | Mosfilm | 1967-02-12 | Drama
630 West Side Story | 105 | United Artists | 1961-01-03 | Musical
631 Bananas | 105 | United Artists | 1971-07-13 | Comedy
632 Yojimbo | 106 | Toho | 1961-06-16 | Drama
633 There's a Girl in my Soup | 107 | Columbia | 1970-06-11 | Comedy
634 Taxi Driver | 107 | Columbia | 1975-05-15 | Action
635 Absence of Malice | 107 | Columbia | 1981-11-15 | Action
636 Storia di una donna | 108 | Westward | 1970-08-15 | Romantic
637 The King and I | 109 | 20th Century Fox | 1956-08-11 | Musical
638 Das Boot | 110 | Bavaria Atelier | 1981-11-11 | Drama
639 Bed Knobs and Broomsticks | 111 | Walt Disney | | Musical
645 To sum the column <literal>len</literal> of all films and group
646 the results by <literal>kind</literal>:
649 SELECT kind, SUM(len) AS total FROM films GROUP BY kind;
663 To sum the column <literal>len</literal> of all films, group
664 the results by <literal>kind</literal> and show those group totals
665 that are less than 5 hours:
668 SELECT kind, SUM(len) AS total
671 HAVING SUM(len) < INTERVAL '5 hour';
682 The following two examples are identical ways of sorting the individual
683 results according to the contents of the second column
684 (<literal>name</literal>):
687 SELECT * FROM distributors ORDER BY name;
688 SELECT * FROM distributors ORDER BY 2;
691 -----+------------------
692 109 | 20th Century Fox
693 110 | Bavaria Atelier
696 102 | Jean Luc Godard
710 This example shows how to obtain the union of the tables
711 <literal>distributors</literal> and
712 <literal>actors</literal>, restricting the results to those that begin
713 with letter W in each table. Only distinct rows are wanted, so the
714 ALL keyword is omitted:
717 distributors: actors:
719 -----+-------------- ----+----------------
720 108 | Westward 1 | Woody Allen
721 111 | Walt Disney 2 | Warren Beatty
722 112 | Warner Bros. 3 | Walter Matthau
725 SELECT distributors.name
727 WHERE distributors.name LIKE 'W%'
731 WHERE actors.name LIKE 'W%'
745 <refsect1 id="R1-SQL-SELECT-3">
750 <refsect2 id="R2-SQL-SELECT-4">
752 <date>1998-09-24</date>
755 <acronym>Extensions</acronym>
759 <productname>Postgres</productname> allows one to omit
760 the <command>FROM</command> clause from a query. This feature
761 was retained from the original PostQuel query language:
763 SELECT distributors.* WHERE name = 'Westwood';
772 <refsect2 id="R2-SQL-SELECT-5">
774 <date>1998-09-24</date>
777 <acronym>SQL92</acronym>
782 <refsect3 id="R3-SQL-SELECT-1">
784 <date>1998-04-15</date>
790 In the <acronym>SQL92</acronym> standard, the optional keyword "AS"
791 is just noise and can be
792 omitted without affecting the meaning.
793 The <productname>Postgres</productname> parser requires this keyword when
794 renaming columns because the type extensibility features lead to
796 in this context.</para>
799 The DISTINCT ON phrase is not part of <acronym>SQL92</acronym>.
800 Nor are LIMIT and OFFSET.
804 In <acronym>SQL92</acronym>, an ORDER BY clause may only use result
805 column names or numbers, while a GROUP BY clause may only use input
807 <productname>Postgres</productname> extends each of these clauses to
808 allow the other choice as well (but it uses the standard's interpretation
809 if there is ambiguity).
810 <productname>Postgres</productname> also allows both clauses to specify
811 arbitrary expressions. Note that names appearing in an expression will
812 always be taken as input-column names, not as result-column names.
816 <refsect3 id="R3-SQL-UNION-1">
818 <date>1998-09-24</date>
824 The <acronym>SQL92</acronym> syntax for UNION allows an
825 additional CORRESPONDING BY clause:
827 <replaceable class="PARAMETER">table_query</replaceable> UNION [ALL]
828 [CORRESPONDING [BY (<replaceable class="PARAMETER">column</replaceable> [,...])]]
829 <replaceable class="PARAMETER">table_query</replaceable>
833 The CORRESPONDING BY clause is not supported by
834 <productname>Postgres</productname>.
842 <!-- Keep this comment at the end of the file
847 sgml-minimize-attributes:nil
848 sgml-always-quote-attributes:t
851 sgml-parent-document:nil
852 sgml-default-dtd-file:"../reference.ced"
853 sgml-exposed-tags:nil
854 sgml-local-catalogs:"/usr/lib/sgml/catalog"
855 sgml-local-ecat-files:nil