1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/queries.sgml,v 1.14 2001/11/28 20:49:10 petere 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 or a derived table such as a
90 subquery, a table join, or complex combinations of these. If more
91 than one table reference is listed in the FROM clause they are
92 cross-joined (see below) to form the derived table that may then
93 be subject to transformations by the WHERE, GROUP BY, and HAVING
94 clauses and is finally the result of the overall table expression.
98 When a table reference names a table that is the
99 supertable of a table inheritance hierarchy, the table reference
100 produces rows of not only that table but all of its subtable successors,
101 unless the keyword ONLY precedes the table name. However, the reference
102 produces only the columns that appear in the named table --- any columns
103 added in subtables are ignored.
106 <sect3 id="queries-join">
107 <title>Joined Tables</title>
109 <indexterm zone="queries-join">
110 <primary>joins</primary>
114 A joined table is a table derived from two other (real or
115 derived) tables according to the rules of the particular join
116 type. INNER, OUTER, and CROSS JOIN are supported.
120 <title>Join Types</title>
123 <term>CROSS JOIN</term>
126 <primary>joins</primary>
127 <secondary>cross</secondary>
132 <replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable>
136 For each combination of rows from
137 <replaceable>T1</replaceable> and
138 <replaceable>T2</replaceable>, the derived table will contain a
139 row consisting of all columns in <replaceable>T1</replaceable>
140 followed by all columns in <replaceable>T2</replaceable>. If
141 the tables have N and M rows respectively, the joined
142 table will have N * M rows. A cross join is equivalent to an
143 <literal>INNER JOIN ON TRUE</literal>.
148 <literal>FROM <replaceable>T1</replaceable> CROSS JOIN
149 <replaceable>T2</replaceable></literal> is equivalent to
150 <literal>FROM <replaceable>T1</replaceable>,
151 <replaceable>T2</replaceable></literal>.
158 <term>Qualified joins</term>
161 <primary>joins</primary>
162 <secondary>outer</secondary>
167 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
168 <replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
169 <replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
173 The words <token>INNER</token> and <token>OUTER</token> are
174 optional for all joins. <token>INNER</token> is the default;
175 <token>LEFT</token>, <token>RIGHT</token>, and
176 <token>FULL</token> imply an OUTER JOIN.
180 The <firstterm>join condition</firstterm> is specified in the
181 ON or USING clause, or implicitly by the word NATURAL. The join
182 condition determines which rows from the two source tables are
183 considered to <quote>match</quote>, as explained in detail below.
187 The ON clause is the most general kind of join condition: it takes a
188 Boolean value expression of the same kind as is used in a WHERE
189 clause. A pair of rows from T1 and T2 match if the ON expression
190 evaluates to TRUE for them.
194 USING is a shorthand notation: it takes a
195 comma-separated list of column names, which the joined tables
196 must have in common, and forms a join condition specifying equality
197 of each of these pairs of columns. Furthermore, the output of
198 a JOIN USING has one column for each of the equated pairs of
199 input columns, followed by all of the other columns from each table.
200 Thus, <literal>USING (a, b, c)</literal> is equivalent to
201 <literal>ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)</literal>
202 with the exception that
203 if ON is used there will be two columns a, b, and c in the
204 result, whereas with USING there will be only one of each.
209 <primary>joins</primary>
210 <secondary>natural</secondary>
212 Finally, NATURAL is a shorthand form of USING: it forms a USING
213 list consisting of exactly those column names that appear in both
214 input tables. As with USING, these columns appear only once in
219 The possible types of qualified JOIN are:
224 <term>INNER JOIN</term>
228 For each row R1 of T1, the joined table has a row for each
229 row in T2 that satisfies the join condition with R1.
235 <term>LEFT OUTER JOIN</term>
238 <primary>joins</primary>
239 <secondary>left</secondary>
244 First, an INNER JOIN is performed. Then, for each row in T1
245 that does not satisfy the join condition with any row in
246 T2, a joined row is returned with NULL values in columns of
247 T2. Thus, the joined table unconditionally has at least one
248 row for each row in T1.
254 <term>RIGHT OUTER JOIN</term>
258 First, an INNER JOIN is performed. Then, for each row in T2
259 that does not satisfy the join condition with any row in
260 T1, a joined row is returned with NULL values in columns of
261 T1. This is the converse of a left join: the result table will
262 unconditionally have a row for each row in T2.
268 <term>FULL OUTER JOIN</term>
272 First, an INNER JOIN is performed. Then, for each row in
273 T1 that does not satisfy the join condition with any row in
274 T2, a joined row is returned with null values in columns of
275 T2. Also, for each row of T2 that does not satisfy the
276 join condition with any row in T1, a joined row with null
277 values in the columns of T1 is returned.
287 Joins of all types can be chained together or nested: either
288 or both of <replaceable>T1</replaceable> and
289 <replaceable>T2</replaceable> may be joined tables. Parentheses
290 may be used around JOIN clauses to control the join order. In the
291 absence of parentheses, JOIN clauses nest left-to-right.
295 <sect3 id="queries-subqueries">
296 <title>Subqueries</title>
298 <indexterm zone="queries-subqueries">
299 <primary>subqueries</primary>
303 Subqueries specifying a derived table must be enclosed in
304 parentheses and <emphasis>must</emphasis> be named using an AS
305 clause. (See <xref linkend="queries-table-aliases">.)
309 FROM (SELECT * FROM table1) AS alias_name
313 This example is equivalent to <literal>FROM table1 AS
314 alias_name</literal>. More interesting cases, which can't be
315 reduced to a plain join, arise when the subquery involves grouping
320 <sect3 id="queries-table-aliases">
321 <title>Table and Column Aliases</title>
323 <indexterm zone="queries-table-aliases">
324 <primary>label</primary>
325 <secondary>table</secondary>
329 <primary>alias</primary>
334 A temporary name can be given to tables and complex table
335 references to be used for references to the derived table in
336 further processing. This is called a <firstterm>table
339 FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable>
341 Here, <replaceable>alias</replaceable> can be any regular
342 identifier. The alias becomes the new name of the table
343 reference for the current query -- it is no longer possible to
344 refer to the table by the original name. Thus
346 SELECT * FROM my_table AS m WHERE my_table.a > 5;
348 is not valid SQL syntax. What will actually happen (this is a
349 <productname>PostgreSQL</productname> extension to the standard)
351 table reference is added to the FROM clause, so the query is
352 processed as if it were written as
354 SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;
356 Table aliases are mainly for notational convenience, but it is
357 necessary to use them when joining a table to itself, e.g.,
359 SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
361 Additionally, an alias is required if the table reference is a
366 Parentheses are used to resolve ambiguities. The following
367 statement will assign the alias <literal>b</literal> to the
368 result of the join, unlike the previous example:
370 SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
376 FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
378 This form is equivalent to the previously treated one; the
379 <token>AS</token> key word is noise.
384 FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> )
387 in addition to renaming the table as described above, the columns
388 of the table are also given temporary names for use by the surrounding
389 query. If fewer column
390 aliases are specified than the actual table has columns, the remaining
391 columns are not renamed. This syntax is especially useful for
392 self-joins or subqueries.
396 When an alias is applied to the output of a JOIN clause, using any of
397 these forms, the alias hides the original names within the JOIN.
400 SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
404 SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
406 is not valid: the table alias A is not visible outside the alias C.
410 <sect3 id="queries-table-expression-examples">
411 <title>Examples</title>
415 FROM T1 INNER JOIN T2 USING (C)
416 FROM T1 LEFT OUTER JOIN T2 USING (C)
417 FROM (T1 RIGHT OUTER JOIN T2 ON (T1.C1=T2.C1)) AS DT1
418 FROM (T1 FULL OUTER JOIN T2 USING (C)) AS DT1 (DT1C1, DT1C2)
420 FROM T1 NATURAL INNER JOIN T2
421 FROM T1 NATURAL LEFT OUTER JOIN T2
422 FROM T1 NATURAL RIGHT OUTER JOIN T2
423 FROM T1 NATURAL FULL OUTER JOIN T2
425 FROM (SELECT * FROM T1) DT1 CROSS JOIN T2, T3
426 FROM (SELECT * FROM T1) DT1, T2, T3
429 Above are some examples of joined tables and complex derived
430 tables. Notice how the AS clause renames or names a derived
431 table and how the optional comma-separated list of column names
432 that follows renames the columns. The last two
433 FROM clauses produce the same derived table from T1, T2, and T3.
434 The AS keyword was omitted in naming the subquery as DT1. The
435 keywords OUTER and INNER are noise that can be omitted also.
441 <sect2 id="queries-where">
442 <title>WHERE clause</title>
444 <indexterm zone="queries-where">
445 <primary>where</primary>
449 The syntax of the WHERE clause is
451 WHERE <replaceable>search_condition</replaceable>
453 where <replaceable>search_condition</replaceable> is any value
454 expression as defined in <xref linkend="sql-expressions"> that
455 returns a value of type <type>boolean</type>.
459 After the processing of the FROM clause is done, each row of the
460 derived table is checked against the search condition. If the
461 result of the condition is true, the row is kept in the output
462 table, otherwise (that is, if the result is false or NULL) it is
463 discarded. The search condition typically references at least some
464 column in the table generated in the FROM clause; this is not
465 required, but otherwise the WHERE clause will be fairly useless.
470 Before the implementation of the JOIN syntax, it was necessary to
471 put the join condition of an inner join in the WHERE clause. For
472 example, these table expressions are equivalent:
474 FROM a, b WHERE a.id = b.id AND b.val > 5
478 FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
482 FROM a NATURAL JOIN b WHERE b.val > 5
484 Which one of these you use is mainly a matter of style. The JOIN
485 syntax in the FROM clause is probably not as portable to other
486 products. For outer joins there is no choice in any case: they
487 must be done in the FROM clause. A ON/USING clause of an outer join
488 is <emphasis>not</> equivalent to a WHERE condition, because it
489 determines the addition of rows (for unmatched input rows) as well
490 as the removal of rows from the final result.
501 C1 IN (SELECT C1 FROM T2)
503 C1 IN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10)
506 C1 BETWEEN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10) AND 100
509 EXISTS (SELECT C1 FROM T2 WHERE C2 > FDT.C1)
513 In the examples above, <literal>FDT</literal> is the table derived
514 in the FROM clause. Rows that do not meet the search condition of
515 the where clause are eliminated from
516 <literal>FDT</literal>. Notice the use of scalar subqueries as
517 value expressions. Just like any other query, the subqueries can
518 employ complex table expressions. Notice how
519 <literal>FDT</literal> is referenced in the subqueries.
520 Qualifying <literal>C1</> as <literal>FDT.C1</> is only necessary
521 if <literal>C1</> is also the name of a column in the derived
522 input table of the subquery. Qualifying the column name adds
523 clarity even when it is not needed. This shows how the column
524 naming scope of an outer query extends into its inner queries.
529 <sect2 id="queries-group">
530 <title>GROUP BY and HAVING clauses</title>
532 <indexterm zone="queries-group">
533 <primary>group</primary>
537 After passing the WHERE filter, the derived input table may be
538 subject to grouping, using the GROUP BY clause, and elimination of
539 group rows using the HAVING clause.
543 SELECT <replaceable>select_list</replaceable>
545 <optional>WHERE ...</optional>
546 GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
550 The GROUP BY clause is used to group together rows in a table that
551 share the same values in all the columns listed. The order in
552 which the columns are listed does not matter (as opposed to an
553 ORDER BY clause). The purpose is to reduce each group of rows
554 sharing common values into one group row that is representative of
555 all rows in the group. This is done to eliminate redundancy in
556 the output and/or obtain aggregates that apply to these groups.
560 Once a table is grouped, columns that are not used in the
561 grouping cannot be referenced except in aggregate expressions,
562 since a specific value in those columns is ambiguous - which row
563 in the group should it come from? The grouped-by columns can be
564 referenced in select list column expressions since they have a
565 known constant value per group. Aggregate functions on the
566 ungrouped columns provide values that span the rows of a group,
567 not of the whole table. For instance, a
568 <function>sum(sales)</function> on a table grouped by product code
569 gives the total sales for each product, not the total sales on all
570 products. Aggregates computed on the ungrouped columns are
571 representative of the group, whereas individual values of an ungrouped
578 SELECT pid, p.name, (sum(s.units) * p.price) AS sales
579 FROM products p LEFT JOIN sales s USING ( pid )
580 GROUP BY pid, p.name, p.price;
582 In this example, the columns <literal>pid</literal>, <literal>p.name</literal>, and <literal>p.price</literal> must be in
583 the GROUP BY clause since they are referenced in the query select
584 list. The column s.units does not have to be in the GROUP BY list
585 since it is only used in an aggregate expression
586 (<function>sum()</function>), which represents the group of sales
587 of a product. For each product, a summary row is returned about
588 all sales of the product.
592 In strict SQL, GROUP BY can only group by columns of the source
593 table but <productname>PostgreSQL</productname> extends this to also allow GROUP BY to group by
594 select columns in the query select list. Grouping by value
595 expressions instead of simple column names is also allowed.
600 SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable>
602 If a table has been grouped using a GROUP BY clause, but then only
603 certain groups are of interest, the HAVING clause can be used,
604 much like a WHERE clause, to eliminate groups from a grouped
605 table. <productname>PostgreSQL</productname> allows a HAVING clause to be
606 used without a GROUP BY, in which case it acts like another WHERE
607 clause, but the point in using HAVING that way is not clear. A good
608 rule of thumb is that a HAVING condition should refer to the results
609 of aggregate functions. A restriction that does not involve an
610 aggregate is more efficiently expressed in the WHERE clause.
616 SELECT pid AS "Products",
617 p.name AS "Over 5000",
618 (sum(s.units) * (p.price - p.cost)) AS "Past Month Profit"
619 FROM products p LEFT JOIN sales s USING ( pid )
620 WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
621 GROUP BY pid, p.name, p.price, p.cost
622 HAVING sum(p.price * s.units) > 5000;
624 In the example above, the WHERE clause is selecting rows by a
625 column that is not grouped, while the HAVING clause
626 restricts the output to groups with total gross sales over 5000.
632 <sect1 id="queries-select-lists">
633 <title>Select Lists</title>
636 <primary>select</primary>
637 <secondary>select list</secondary>
641 As shown in the previous section,
642 the table expression in the <command>SELECT</command> command
643 constructs an intermediate virtual table by possibly combining
644 tables, views, eliminating rows, grouping, etc. This table is
645 finally passed on to processing by the <firstterm>select list</firstterm>. The select
646 list determines which <emphasis>columns</emphasis> of the
647 intermediate table are actually output. The simplest kind of select list
648 is <literal>*</literal> which emits all columns that the table
649 expression produces. Otherwise, a select list is a comma-separated
650 list of value expressions (as defined in <xref
651 linkend="sql-expressions">). For instance, it could be a list of
654 SELECT a, b, c FROM ...
656 The columns names a, b, and c are either the actual names of the
657 columns of tables referenced in the FROM clause, or the aliases
658 given to them as explained in <xref linkend="queries-table-aliases">.
659 The name space available in the select list is the same as in the
660 WHERE clause (unless grouping is used, in which case it is the same
661 as in the HAVING clause). If more than one table has a column of
662 the same name, the table name must also be given, as in
664 SELECT tbl1.a, tbl2.b, tbl1.c FROM ...
666 (see also <xref linkend="queries-where">).
670 If an arbitrary value expression is used in the select list, it
671 conceptually adds a new virtual column to the returned table. The
672 value expression is evaluated once for each retrieved
673 row, with the row's values substituted for any column references. But
674 the expressions in the select list do not have to reference any
675 columns in the table expression of the FROM clause; they could be
676 constant arithmetic expressions as well, for instance.
679 <sect2 id="queries-column-labels">
680 <title>Column Labels</title>
682 <indexterm zone="queries-column-labels">
683 <primary>label</primary>
684 <secondary>column</secondary>
688 The entries in the select list can be assigned names for further
689 processing. The <quote>further processing</quote> in this case is
690 an optional sort specification and the client application (e.g.,
691 column headers for display). For example:
693 SELECT a AS value, b + c AS sum FROM ...
698 If no output column name is specified via AS, the system assigns a
699 default name. For simple column references, this is the name of the
700 referenced column. For function
701 calls, this is the name of the function. For complex expressions,
702 the system will generate a generic name.
707 The naming of output columns here is different from that done in
708 the FROM clause (see <xref linkend="queries-table-aliases">). This
709 pipeline will in fact allow you to rename the same column twice,
710 but the name chosen in the select list is the one that will be
716 <sect2 id="queries-distinct">
717 <title>DISTINCT</title>
719 <indexterm zone="queries-distinct">
720 <primary>distinct</primary>
724 After the select list has been processed, the result table may
725 optionally be subject to the elimination of duplicates. The
726 <token>DISTINCT</token> key word is written directly after the
727 <token>SELECT</token> to enable this:
729 SELECT DISTINCT <replaceable>select_list</replaceable> ...
731 (Instead of <token>DISTINCT</token> the word <token>ALL</token>
732 can be used to select the default behavior of retaining all rows.)
736 Obviously, two rows are considered distinct if they differ in at
737 least one column value. NULLs are considered equal in this
742 Alternatively, an arbitrary expression can determine what rows are
743 to be considered distinct:
745 SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ...
747 Here <replaceable>expression</replaceable> is an arbitrary value
748 expression that is evaluated for all rows. A set of rows for
749 which all the expressions are equal are considered duplicates, and
750 only the first row of the set is kept in the output. Note that the
751 <quote>first row</quote> of a set is unpredictable unless the
752 query is sorted on enough columns to guarantee a unique ordering
753 of the rows arriving at the DISTINCT filter. (DISTINCT ON processing
754 occurs after ORDER BY sorting.)
758 The DISTINCT ON clause is not part of the SQL standard and is
759 sometimes considered bad style because of the potentially indeterminate
761 of its results. With judicious use of GROUP BY and subselects in
762 FROM the construct can be avoided, but it is very often the most
763 convenient alternative.
768 <sect1 id="queries-union">
769 <title>Combining Queries</title>
771 <indexterm zone="queries-union">
772 <primary>union</primary>
774 <indexterm zone="queries-union">
775 <primary>intersection</primary>
777 <indexterm zone="queries-union">
778 <primary>except</primary>
782 The results of two queries can be combined using the set operations
783 union, intersection, and difference. The syntax is
785 <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
786 <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
787 <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
789 <replaceable>query1</replaceable> and
790 <replaceable>query2</replaceable> are queries that can use any of
791 the features discussed up to this point. Set operations can also
792 be nested and chained, for example
794 <replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> UNION <replaceable>query3</replaceable>
798 (<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) UNION <replaceable>query3</replaceable>
803 <command>UNION</command> effectively appends the result of
804 <replaceable>query2</replaceable> to the result of
805 <replaceable>query1</replaceable> (although there is no guarantee
806 that this is the order in which the rows are actually returned).
807 Furthermore, it eliminates all duplicate rows, in the sense of DISTINCT,
808 unless ALL is specified.
812 <command>INTERSECT</command> returns all rows that are both in the
813 result of <replaceable>query1</replaceable> and in the result of
814 <replaceable>query2</replaceable>. Duplicate rows are eliminated
815 unless ALL is specified.
819 <command>EXCEPT</command> returns all rows that are in the result
820 of <replaceable>query1</replaceable> but not in the result of
821 <replaceable>query2</replaceable>. Again, duplicates are
822 eliminated unless ALL is specified.
826 In order to calculate the union, intersection, or difference of two
827 queries, the two queries must be <quote>union compatible</quote>,
828 which means that they both return the same number of columns, and
829 that the corresponding columns have compatible data types, as
830 described in <xref linkend="typeconv-union-case">.
835 <sect1 id="queries-order">
836 <title>Sorting Rows</title>
838 <indexterm zone="queries-order">
839 <primary>sorting</primary>
840 <secondary>query results</secondary>
844 After a query has produced an output table (after the select list
845 has been processed) it can optionally be sorted. If sorting is not
846 chosen, the rows will be returned in random order. The actual
847 order in that case will depend on the scan and join plan types and
848 the order on disk, but it must not be relied on. A particular
849 output ordering can only be guaranteed if the sort step is explicitly
854 The ORDER BY clause specifies the sort order:
856 SELECT <replaceable>select_list</replaceable>
857 FROM <replaceable>table_expression</replaceable>
858 ORDER BY <replaceable>column1</replaceable> <optional>ASC | DESC</optional> <optional>, <replaceable>column2</replaceable> <optional>ASC | DESC</optional> ...</optional>
860 <replaceable>column1</replaceable>, etc., refer to select list
861 columns. These can be either the output name of a column (see
862 <xref linkend="queries-column-labels">) or the number of a column. Some
865 SELECT a, b FROM table1 ORDER BY a;
866 SELECT a + b AS sum, c FROM table1 ORDER BY sum;
867 SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;
872 As an extension to the SQL standard, <productname>PostgreSQL</productname> also allows ordering
873 by arbitrary expressions:
875 SELECT a, b FROM table1 ORDER BY a + b;
877 References to column names in the FROM clause that are renamed in
878 the select list are also allowed:
880 SELECT a AS b FROM table1 ORDER BY a;
882 But these extensions do not work in queries involving UNION, INTERSECT,
883 or EXCEPT, and are not portable to other <acronym>DBMS</acronym>.
887 Each column specification may be followed by an optional <token>ASC</token> or
888 <token>DESC</token> to set the sort direction. <token>ASC</token> is default. Ascending order
889 puts smaller values first, where <quote>smaller</quote> is defined
890 in terms of the <literal><</literal> operator. Similarly,
891 descending order is determined with the <literal>></literal>
896 If more than one sort column is specified, the later entries are
897 used to sort rows that are equal under the order imposed by the
898 earlier sort specifications.
902 <sect1 id="queries-limit">
903 <title>LIMIT and OFFSET</title>
905 <indexterm zone="queries-limit">
906 <primary>limit</primary>
909 <indexterm zone="queries-limit">
910 <primary>offset</primary>
911 <secondary>with query results</secondary>
915 SELECT <replaceable>select_list</replaceable>
916 FROM <replaceable>table_expression</replaceable>
917 <optional>LIMIT { <replaceable>number</replaceable> | ALL }</optional> <optional>OFFSET <replaceable>number</replaceable></optional>
921 LIMIT allows you to retrieve just a portion of the rows that are
922 generated by the rest of the query. If a limit count is given, no
923 more than that many rows will be returned.
924 LIMIT ALL is the same as omitting a LIMIT clause.
928 OFFSET says to skip that many rows before beginning to return rows
929 to the client. OFFSET 0 is the same as omitting an OFFSET clause.
930 If both OFFSET and LIMIT appear, then OFFSET rows are skipped before
931 starting to count the LIMIT rows that are returned.
935 When using LIMIT, it is a good idea to use an ORDER BY clause that
936 constrains the result rows into a unique order. Otherwise you will
937 get an unpredictable subset of the query's rows---you may be asking
938 for the tenth through twentieth rows, but tenth through twentieth
939 in what ordering? The ordering is unknown, unless you specified
944 The query optimizer takes LIMIT into account when generating a
945 query plan, so you are very likely to get different plans (yielding
946 different row orders) depending on what you give for LIMIT and
947 OFFSET. Thus, using different LIMIT/OFFSET values to select
948 different subsets of a query result <emphasis>will give
949 inconsistent results</emphasis> unless you enforce a predictable
950 result ordering with ORDER BY. This is not a bug; it is an
951 inherent consequence of the fact that SQL does not promise to
952 deliver the results of a query in any particular order unless ORDER
953 BY is used to constrain the order.