1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/queries.sgml,v 1.15 2002/04/25 20:14:43 tgl Exp $ -->
6 <sect1 id="queries-overview">
7 <title>Overview</title>
10 A <firstterm>query</firstterm> is the process of retrieving or the command
11 to retrieve data from a database. In SQL the <command>SELECT</command>
12 command is used to specify queries. The general syntax of the
13 <command>SELECT</command> command is
15 SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>
17 The following sections describe the details of the select list, the
18 table expression, and the sort specification. The simplest kind of
23 Assuming that there is a table called table1, this command would
24 retrieve all rows and all columns from table1. (The method of
25 retrieval depends on the client application. For example, the
26 <application>psql</application> program will display an ASCII-art
27 table on the screen, client libraries will offer functions to
28 retrieve individual rows and columns.) The select list
29 specification <literal>*</literal> means all columns that the table
30 expression happens to provide. A select list can also select a
31 subset of the available columns or even make calculations on the
32 columns before retrieving them; see <xref
33 linkend="queries-select-lists">. For example, if table1 has columns
34 named a, b, and c (and perhaps others) you can make the following
37 SELECT a, b + c FROM table1;
39 (assuming that b and c are of a numeric data type).
43 <literal>FROM table1</literal> is a particularly simple kind of
44 table expression. In general, table expressions can be complex
45 constructs of base tables, joins, and subqueries. But you can also
46 omit the table expression entirely and use the SELECT command as a
51 This is more useful if the expressions in the select list return
52 varying results. For example, you could call a function this way.
59 <sect1 id="queries-table-expressions">
60 <title>Table Expressions</title>
63 A <firstterm>table expression</firstterm> specifies a table. The
64 table expression contains a FROM clause that is optionally followed
65 by WHERE, GROUP BY, and HAVING clauses. Trivial table expressions
66 simply refer to a table on disk, a so-called base table, but more
67 complex expressions can be used to modify or combine base tables in
72 The optional WHERE, GROUP BY, and HAVING clauses in the table expression
73 specify a pipeline of successive transformations performed on the
74 table derived in the FROM clause. The derived table that is produced by
75 all these transformations provides the input rows used to compute output
76 rows as specified by the select list of column value expressions.
79 <sect2 id="queries-from">
80 <title>FROM clause</title>
83 The FROM clause derives a table from one or more other tables
84 given in a comma-separated table reference list.
86 FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional>
89 A table reference may be a table name (possibly schema-qualified),
90 or a derived table such as a
91 subquery, a table join, or complex combinations of these. If more
92 than one table reference is listed in the FROM clause they are
93 cross-joined (see below) to form the derived table that may then
94 be subject to transformations by the WHERE, GROUP BY, and HAVING
95 clauses and is finally the result of the overall table expression.
99 When a table reference names a table that is the
100 supertable of a table inheritance hierarchy, the table reference
101 produces rows of not only that table but all of its subtable successors,
102 unless the keyword ONLY precedes the table name. However, the reference
103 produces only the columns that appear in the named table --- any columns
104 added in subtables are ignored.
107 <sect3 id="queries-join">
108 <title>Joined Tables</title>
110 <indexterm zone="queries-join">
111 <primary>joins</primary>
115 A joined table is a table derived from two other (real or
116 derived) tables according to the rules of the particular join
117 type. INNER, OUTER, and CROSS JOIN are supported.
121 <title>Join Types</title>
124 <term>CROSS JOIN</term>
127 <primary>joins</primary>
128 <secondary>cross</secondary>
133 <replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
137 For each combination of rows from
138 <replaceable>T1</replaceable> and
139 <replaceable>T2</replaceable>, the derived table will contain a
140 row consisting of all columns in <replaceable>T1</replaceable>
141 followed by all columns in <replaceable>T2</replaceable>. If
142 the tables have N and M rows respectively, the joined
143 table will have N * M rows. A cross join is equivalent to an
144 <literal>INNER JOIN ON TRUE</literal>.
149 <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
150 <replaceable>T2</replaceable></literal> is equivalent to
151 <literal>FROM <replaceable>T1</replaceable>,
152 <replaceable>T2</replaceable></literal>.
159 <term>Qualified joins</term>
162 <primary>joins</primary>
163 <secondary>outer</secondary>
168 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
169 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
170 <replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
174 The words <token>INNER</token> and <token>OUTER</token> are
175 optional for all joins. <token>INNER</token> is the default;
176 <token>LEFT</token>, <token>RIGHT</token>, and
177 <token>FULL</token> imply an OUTER JOIN.
181 The <firstterm>join condition</firstterm> is specified in the
182 ON or USING clause, or implicitly by the word NATURAL. The join
183 condition determines which rows from the two source tables are
184 considered to <quote>match</quote>, as explained in detail below.
188 The ON clause is the most general kind of join condition: it takes a
189 Boolean value expression of the same kind as is used in a WHERE
190 clause. A pair of rows from T1 and T2 match if the ON expression
191 evaluates to TRUE for them.
195 USING is a shorthand notation: it takes a
196 comma-separated list of column names, which the joined tables
197 must have in common, and forms a join condition specifying equality
198 of each of these pairs of columns. Furthermore, the output of
199 a JOIN USING has one column for each of the equated pairs of
200 input columns, followed by all of the other columns from each table.
201 Thus, <literal>USING (a, b, c)</literal> is equivalent to
202 <literal>ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)</literal>
203 with the exception that
204 if ON is used there will be two columns a, b, and c in the
205 result, whereas with USING there will be only one of each.
210 <primary>joins</primary>
211 <secondary>natural</secondary>
213 Finally, NATURAL is a shorthand form of USING: it forms a USING
214 list consisting of exactly those column names that appear in both
215 input tables. As with USING, these columns appear only once in
220 The possible types of qualified JOIN are:
225 <term>INNER JOIN</term>
229 For each row R1 of T1, the joined table has a row for each
230 row in T2 that satisfies the join condition with R1.
236 <term>LEFT OUTER JOIN</term>
239 <primary>joins</primary>
240 <secondary>left</secondary>
245 First, an INNER JOIN is performed. Then, for each row in T1
246 that does not satisfy the join condition with any row in
247 T2, a joined row is returned with NULL values in columns of
248 T2. Thus, the joined table unconditionally has at least one
249 row for each row in T1.
255 <term>RIGHT OUTER JOIN</term>
259 First, an INNER JOIN is performed. Then, for each row in T2
260 that does not satisfy the join condition with any row in
261 T1, a joined row is returned with NULL values in columns of
262 T1. This is the converse of a left join: the result table will
263 unconditionally have a row for each row in T2.
269 <term>FULL OUTER JOIN</term>
273 First, an INNER JOIN is performed. Then, for each row in
274 T1 that does not satisfy the join condition with any row in
275 T2, a joined row is returned with null values in columns of
276 T2. Also, for each row of T2 that does not satisfy the
277 join condition with any row in T1, a joined row with null
278 values in the columns of T1 is returned.
288 Joins of all types can be chained together or nested: either
289 or both of <replaceable>T1</replaceable> and
290 <replaceable>T2</replaceable> may be joined tables. Parentheses
291 may be used around JOIN clauses to control the join order. In the
292 absence of parentheses, JOIN clauses nest left-to-right.
296 <sect3 id="queries-subqueries">
297 <title>Subqueries</title>
299 <indexterm zone="queries-subqueries">
300 <primary>subqueries</primary>
304 Subqueries specifying a derived table must be enclosed in
305 parentheses and <emphasis>must</emphasis> be named using an AS
306 clause. (See <xref linkend="queries-table-aliases">.)
310 FROM (SELECT * FROM table1) AS alias_name
314 This example is equivalent to <literal>FROM table1 AS
315 alias_name</literal>. More interesting cases, which can't be
316 reduced to a plain join, arise when the subquery involves grouping
321 <sect3 id="queries-table-aliases">
322 <title>Table and Column Aliases</title>
324 <indexterm zone="queries-table-aliases">
325 <primary>label</primary>
326 <secondary>table</secondary>
330 <primary>alias</primary>
335 A temporary name can be given to tables and complex table
336 references to be used for references to the derived table in
337 further processing. This is called a <firstterm>table
340 FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable>
342 Here, <replaceable>alias</replaceable> can be any regular
343 identifier. The alias becomes the new name of the table
344 reference for the current query -- it is no longer possible to
345 refer to the table by the original name. Thus
347 SELECT * FROM my_table AS m WHERE my_table.a > 5;
349 is not valid SQL syntax. What will actually happen (this is a
350 <productname>PostgreSQL</productname> extension to the standard)
352 table reference is added to the FROM clause, so the query is
353 processed as if it were written as
355 SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;
357 Table aliases are mainly for notational convenience, but it is
358 necessary to use them when joining a table to itself, e.g.,
360 SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
362 Additionally, an alias is required if the table reference is a
367 Parentheses are used to resolve ambiguities. The following
368 statement will assign the alias <literal>b</literal> to the
369 result of the join, unlike the previous example:
371 SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
377 FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
379 This form is equivalent to the previously treated one; the
380 <token>AS</token> key word is noise.
385 FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> )
388 in addition to renaming the table as described above, the columns
389 of the table are also given temporary names for use by the surrounding
390 query. If fewer column
391 aliases are specified than the actual table has columns, the remaining
392 columns are not renamed. This syntax is especially useful for
393 self-joins or subqueries.
397 When an alias is applied to the output of a JOIN clause, using any of
398 these forms, the alias hides the original names within the JOIN.
401 SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
405 SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
407 is not valid: the table alias A is not visible outside the alias C.
411 <sect3 id="queries-table-expression-examples">
412 <title>Examples</title>
416 FROM T1 INNER JOIN T2 USING (C)
417 FROM T1 LEFT OUTER JOIN T2 USING (C)
418 FROM (T1 RIGHT OUTER JOIN T2 ON (T1.C1=T2.C1)) AS DT1
419 FROM (T1 FULL OUTER JOIN T2 USING (C)) AS DT1 (DT1C1, DT1C2)
421 FROM T1 NATURAL INNER JOIN T2
422 FROM T1 NATURAL LEFT OUTER JOIN T2
423 FROM T1 NATURAL RIGHT OUTER JOIN T2
424 FROM T1 NATURAL FULL OUTER JOIN T2
426 FROM (SELECT * FROM T1) DT1 CROSS JOIN T2, T3
427 FROM (SELECT * FROM T1) DT1, T2, T3
430 Above are some examples of joined tables and complex derived
431 tables. Notice how the AS clause renames or names a derived
432 table and how the optional comma-separated list of column names
433 that follows renames the columns. The last two
434 FROM clauses produce the same derived table from T1, T2, and T3.
435 The AS keyword was omitted in naming the subquery as DT1. The
436 keywords OUTER and INNER are noise that can be omitted also.
442 <sect2 id="queries-where">
443 <title>WHERE clause</title>
445 <indexterm zone="queries-where">
446 <primary>where</primary>
450 The syntax of the WHERE clause is
452 WHERE <replaceable>search_condition</replaceable>
454 where <replaceable>search_condition</replaceable> is any value
455 expression as defined in <xref linkend="sql-expressions"> that
456 returns a value of type <type>boolean</type>.
460 After the processing of the FROM clause is done, each row of the
461 derived table is checked against the search condition. If the
462 result of the condition is true, the row is kept in the output
463 table, otherwise (that is, if the result is false or NULL) it is
464 discarded. The search condition typically references at least some
465 column in the table generated in the FROM clause; this is not
466 required, but otherwise the WHERE clause will be fairly useless.
471 Before the implementation of the JOIN syntax, it was necessary to
472 put the join condition of an inner join in the WHERE clause. For
473 example, these table expressions are equivalent:
475 FROM a, b WHERE a.id = b.id AND b.val > 5
479 FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
483 FROM a NATURAL JOIN b WHERE b.val > 5
485 Which one of these you use is mainly a matter of style. The JOIN
486 syntax in the FROM clause is probably not as portable to other
487 products. For outer joins there is no choice in any case: they
488 must be done in the FROM clause. A ON/USING clause of an outer join
489 is <emphasis>not</> equivalent to a WHERE condition, because it
490 determines the addition of rows (for unmatched input rows) as well
491 as the removal of rows from the final result.
502 C1 IN (SELECT C1 FROM T2)
504 C1 IN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10)
507 C1 BETWEEN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10) AND 100
510 EXISTS (SELECT C1 FROM T2 WHERE C2 > FDT.C1)
514 In the examples above, <literal>FDT</literal> is the table derived
515 in the FROM clause. Rows that do not meet the search condition of
516 the where clause are eliminated from
517 <literal>FDT</literal>. Notice the use of scalar subqueries as
518 value expressions. Just like any other query, the subqueries can
519 employ complex table expressions. Notice how
520 <literal>FDT</literal> is referenced in the subqueries.
521 Qualifying <literal>C1</> as <literal>FDT.C1</> is only necessary
522 if <literal>C1</> is also the name of a column in the derived
523 input table of the subquery. Qualifying the column name adds
524 clarity even when it is not needed. This shows how the column
525 naming scope of an outer query extends into its inner queries.
530 <sect2 id="queries-group">
531 <title>GROUP BY and HAVING clauses</title>
533 <indexterm zone="queries-group">
534 <primary>group</primary>
538 After passing the WHERE filter, the derived input table may be
539 subject to grouping, using the GROUP BY clause, and elimination of
540 group rows using the HAVING clause.
544 SELECT <replaceable>select_list</replaceable>
546 <optional>WHERE ...</optional>
547 GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
551 The GROUP BY clause is used to group together rows in a table that
552 share the same values in all the columns listed. The order in
553 which the columns are listed does not matter (as opposed to an
554 ORDER BY clause). The purpose is to reduce each group of rows
555 sharing common values into one group row that is representative of
556 all rows in the group. This is done to eliminate redundancy in
557 the output and/or obtain aggregates that apply to these groups.
561 Once a table is grouped, columns that are not used in the
562 grouping cannot be referenced except in aggregate expressions,
563 since a specific value in those columns is ambiguous - which row
564 in the group should it come from? The grouped-by columns can be
565 referenced in select list column expressions since they have a
566 known constant value per group. Aggregate functions on the
567 ungrouped columns provide values that span the rows of a group,
568 not of the whole table. For instance, a
569 <function>sum(sales)</function> on a table grouped by product code
570 gives the total sales for each product, not the total sales on all
571 products. Aggregates computed on the ungrouped columns are
572 representative of the group, whereas individual values of an ungrouped
579 SELECT pid, p.name, (sum(s.units) * p.price) AS sales
580 FROM products p LEFT JOIN sales s USING ( pid )
581 GROUP BY pid, p.name, p.price;
583 In this example, the columns <literal>pid</literal>, <literal>p.name</literal>, and <literal>p.price</literal> must be in
584 the GROUP BY clause since they are referenced in the query select
585 list. The column s.units does not have to be in the GROUP BY list
586 since it is only used in an aggregate expression
587 (<function>sum()</function>), which represents the group of sales
588 of a product. For each product, a summary row is returned about
589 all sales of the product.
593 In strict SQL, GROUP BY can only group by columns of the source
594 table but <productname>PostgreSQL</productname> extends this to also allow GROUP BY to group by
595 select columns in the query select list. Grouping by value
596 expressions instead of simple column names is also allowed.
601 SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable>
603 If a table has been grouped using a GROUP BY clause, but then only
604 certain groups are of interest, the HAVING clause can be used,
605 much like a WHERE clause, to eliminate groups from a grouped
606 table. <productname>PostgreSQL</productname> allows a HAVING clause to be
607 used without a GROUP BY, in which case it acts like another WHERE
608 clause, but the point in using HAVING that way is not clear. A good
609 rule of thumb is that a HAVING condition should refer to the results
610 of aggregate functions. A restriction that does not involve an
611 aggregate is more efficiently expressed in the WHERE clause.
617 SELECT pid AS "Products",
618 p.name AS "Over 5000",
619 (sum(s.units) * (p.price - p.cost)) AS "Past Month Profit"
620 FROM products p LEFT JOIN sales s USING ( pid )
621 WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
622 GROUP BY pid, p.name, p.price, p.cost
623 HAVING sum(p.price * s.units) > 5000;
625 In the example above, the WHERE clause is selecting rows by a
626 column that is not grouped, while the HAVING clause
627 restricts the output to groups with total gross sales over 5000.
633 <sect1 id="queries-select-lists">
634 <title>Select Lists</title>
637 <primary>select</primary>
638 <secondary>select list</secondary>
642 As shown in the previous section,
643 the table expression in the <command>SELECT</command> command
644 constructs an intermediate virtual table by possibly combining
645 tables, views, eliminating rows, grouping, etc. This table is
646 finally passed on to processing by the <firstterm>select list</firstterm>. The select
647 list determines which <emphasis>columns</emphasis> of the
648 intermediate table are actually output. The simplest kind of select list
649 is <literal>*</literal> which emits all columns that the table
650 expression produces. Otherwise, a select list is a comma-separated
651 list of value expressions (as defined in <xref
652 linkend="sql-expressions">). For instance, it could be a list of
655 SELECT a, b, c FROM ...
657 The columns names a, b, and c are either the actual names of the
658 columns of tables referenced in the FROM clause, or the aliases
659 given to them as explained in <xref linkend="queries-table-aliases">.
660 The name space available in the select list is the same as in the
661 WHERE clause (unless grouping is used, in which case it is the same
662 as in the HAVING clause). If more than one table has a column of
663 the same name, the table name must also be given, as in
665 SELECT tbl1.a, tbl2.b, tbl1.c FROM ...
667 (see also <xref linkend="queries-where">).
671 If an arbitrary value expression is used in the select list, it
672 conceptually adds a new virtual column to the returned table. The
673 value expression is evaluated once for each retrieved
674 row, with the row's values substituted for any column references. But
675 the expressions in the select list do not have to reference any
676 columns in the table expression of the FROM clause; they could be
677 constant arithmetic expressions as well, for instance.
680 <sect2 id="queries-column-labels">
681 <title>Column Labels</title>
683 <indexterm zone="queries-column-labels">
684 <primary>label</primary>
685 <secondary>column</secondary>
689 The entries in the select list can be assigned names for further
690 processing. The <quote>further processing</quote> in this case is
691 an optional sort specification and the client application (e.g.,
692 column headers for display). For example:
694 SELECT a AS value, b + c AS sum FROM ...
699 If no output column name is specified via AS, the system assigns a
700 default name. For simple column references, this is the name of the
701 referenced column. For function
702 calls, this is the name of the function. For complex expressions,
703 the system will generate a generic name.
708 The naming of output columns here is different from that done in
709 the FROM clause (see <xref linkend="queries-table-aliases">). This
710 pipeline will in fact allow you to rename the same column twice,
711 but the name chosen in the select list is the one that will be
717 <sect2 id="queries-distinct">
718 <title>DISTINCT</title>
720 <indexterm zone="queries-distinct">
721 <primary>distinct</primary>
725 After the select list has been processed, the result table may
726 optionally be subject to the elimination of duplicates. The
727 <token>DISTINCT</token> key word is written directly after the
728 <token>SELECT</token> to enable this:
730 SELECT DISTINCT <replaceable>select_list</replaceable> ...
732 (Instead of <token>DISTINCT</token> the word <token>ALL</token>
733 can be used to select the default behavior of retaining all rows.)
737 Obviously, two rows are considered distinct if they differ in at
738 least one column value. NULLs are considered equal in this
743 Alternatively, an arbitrary expression can determine what rows are
744 to be considered distinct:
746 SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ...
748 Here <replaceable>expression</replaceable> is an arbitrary value
749 expression that is evaluated for all rows. A set of rows for
750 which all the expressions are equal are considered duplicates, and
751 only the first row of the set is kept in the output. Note that the
752 <quote>first row</quote> of a set is unpredictable unless the
753 query is sorted on enough columns to guarantee a unique ordering
754 of the rows arriving at the DISTINCT filter. (DISTINCT ON processing
755 occurs after ORDER BY sorting.)
759 The DISTINCT ON clause is not part of the SQL standard and is
760 sometimes considered bad style because of the potentially indeterminate
762 of its results. With judicious use of GROUP BY and subselects in
763 FROM the construct can be avoided, but it is very often the most
764 convenient alternative.
769 <sect1 id="queries-union">
770 <title>Combining Queries</title>
772 <indexterm zone="queries-union">
773 <primary>union</primary>
775 <indexterm zone="queries-union">
776 <primary>intersection</primary>
778 <indexterm zone="queries-union">
779 <primary>except</primary>
783 The results of two queries can be combined using the set operations
784 union, intersection, and difference. The syntax is
786 <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
787 <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
788 <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
790 <replaceable>query1</replaceable> and
791 <replaceable>query2</replaceable> are queries that can use any of
792 the features discussed up to this point. Set operations can also
793 be nested and chained, for example
795 <replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> UNION <replaceable>query3</replaceable>
799 (<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) UNION <replaceable>query3</replaceable>
804 <command>UNION</command> effectively appends the result of
805 <replaceable>query2</replaceable> to the result of
806 <replaceable>query1</replaceable> (although there is no guarantee
807 that this is the order in which the rows are actually returned).
808 Furthermore, it eliminates all duplicate rows, in the sense of DISTINCT,
809 unless ALL is specified.
813 <command>INTERSECT</command> returns all rows that are both in the
814 result of <replaceable>query1</replaceable> and in the result of
815 <replaceable>query2</replaceable>. Duplicate rows are eliminated
816 unless ALL is specified.
820 <command>EXCEPT</command> returns all rows that are in the result
821 of <replaceable>query1</replaceable> but not in the result of
822 <replaceable>query2</replaceable>. Again, duplicates are
823 eliminated unless ALL is specified.
827 In order to calculate the union, intersection, or difference of two
828 queries, the two queries must be <quote>union compatible</quote>,
829 which means that they both return the same number of columns, and
830 that the corresponding columns have compatible data types, as
831 described in <xref linkend="typeconv-union-case">.
836 <sect1 id="queries-order">
837 <title>Sorting Rows</title>
839 <indexterm zone="queries-order">
840 <primary>sorting</primary>
841 <secondary>query results</secondary>
845 After a query has produced an output table (after the select list
846 has been processed) it can optionally be sorted. If sorting is not
847 chosen, the rows will be returned in random order. The actual
848 order in that case will depend on the scan and join plan types and
849 the order on disk, but it must not be relied on. A particular
850 output ordering can only be guaranteed if the sort step is explicitly
855 The ORDER BY clause specifies the sort order:
857 SELECT <replaceable>select_list</replaceable>
858 FROM <replaceable>table_expression</replaceable>
859 ORDER BY <replaceable>column1</replaceable> <optional>ASC | DESC</optional> <optional>, <replaceable>column2</replaceable> <optional>ASC | DESC</optional> ...</optional>
861 <replaceable>column1</replaceable>, etc., refer to select list
862 columns. These can be either the output name of a column (see
863 <xref linkend="queries-column-labels">) or the number of a column. Some
866 SELECT a, b FROM table1 ORDER BY a;
867 SELECT a + b AS sum, c FROM table1 ORDER BY sum;
868 SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;
873 As an extension to the SQL standard, <productname>PostgreSQL</productname> also allows ordering
874 by arbitrary expressions:
876 SELECT a, b FROM table1 ORDER BY a + b;
878 References to column names in the FROM clause that are renamed in
879 the select list are also allowed:
881 SELECT a AS b FROM table1 ORDER BY a;
883 But these extensions do not work in queries involving UNION, INTERSECT,
884 or EXCEPT, and are not portable to other <acronym>DBMS</acronym>.
888 Each column specification may be followed by an optional <token>ASC</token> or
889 <token>DESC</token> to set the sort direction. <token>ASC</token> is default. Ascending order
890 puts smaller values first, where <quote>smaller</quote> is defined
891 in terms of the <literal><</literal> operator. Similarly,
892 descending order is determined with the <literal>></literal>
897 If more than one sort column is specified, the later entries are
898 used to sort rows that are equal under the order imposed by the
899 earlier sort specifications.
903 <sect1 id="queries-limit">
904 <title>LIMIT and OFFSET</title>
906 <indexterm zone="queries-limit">
907 <primary>limit</primary>
910 <indexterm zone="queries-limit">
911 <primary>offset</primary>
912 <secondary>with query results</secondary>
916 SELECT <replaceable>select_list</replaceable>
917 FROM <replaceable>table_expression</replaceable>
918 <optional>LIMIT { <replaceable>number</replaceable> | ALL }</optional> <optional>OFFSET <replaceable>number</replaceable></optional>
922 LIMIT allows you to retrieve just a portion of the rows that are
923 generated by the rest of the query. If a limit count is given, no
924 more than that many rows will be returned.
925 LIMIT ALL is the same as omitting a LIMIT clause.
929 OFFSET says to skip that many rows before beginning to return rows
930 to the client. OFFSET 0 is the same as omitting an OFFSET clause.
931 If both OFFSET and LIMIT appear, then OFFSET rows are skipped before
932 starting to count the LIMIT rows that are returned.
936 When using LIMIT, it is a good idea to use an ORDER BY clause that
937 constrains the result rows into a unique order. Otherwise you will
938 get an unpredictable subset of the query's rows---you may be asking
939 for the tenth through twentieth rows, but tenth through twentieth
940 in what ordering? The ordering is unknown, unless you specified
945 The query optimizer takes LIMIT into account when generating a
946 query plan, so you are very likely to get different plans (yielding
947 different row orders) depending on what you give for LIMIT and
948 OFFSET. Thus, using different LIMIT/OFFSET values to select
949 different subsets of a query result <emphasis>will give
950 inconsistent results</emphasis> unless you enforce a predictable
951 result ordering with ORDER BY. This is not a bug; it is an
952 inherent consequence of the fact that SQL does not promise to
953 deliver the results of a query in any particular order unless ORDER
954 BY is used to constrain the order.