Skip to content

Commit 71d2446

Browse files
tglsfdctglsfdc
committed
Document the DISTINCT noise word in the UNION/INTERSECT/EXCEPT constructs.
I also rearranged the order of the sections to match the logical order of processing steps: the distinct-elimination implied by SELECT DISTINCT happens before, not after, any UNION/INTERSECT/EXCEPT combination. Per a suggestion from Hitoshi Harada.
1 parent 0c9b166 commit 71d2446

File tree

2 files changed

+69
-59
lines changed

2 files changed

+69
-59
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 68 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
4040
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
4141
[ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
4242
[ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
43-
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
43+
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
4444
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
4545
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
4646
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
@@ -106,7 +106,7 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
106106
<listitem>
107107
<para>
108108
If the <literal>GROUP BY</literal> clause is specified, the
109-
output is divided into groups of rows that match on one or more
109+
output is combined into groups of rows that match on one or more
110110
values. If the <literal>HAVING</literal> clause is present, it
111111
eliminates groups that do not satisfy the given condition. (See
112112
<xref linkend="sql-groupby" endterm="sql-groupby-title"> and
@@ -118,12 +118,23 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
118118
<para>
119119
The actual output rows are computed using the
120120
<command>SELECT</command> output expressions for each selected
121-
row. (See
121+
row or row group. (See
122122
<xref linkend="sql-select-list" endterm="sql-select-list-title">
123123
below.)
124124
</para>
125125
</listitem>
126126

127+
<listitem>
128+
<para>
129+
<literal>SELECT DISTINCT</literal> eliminates duplicate rows from the
130+
result. <literal>SELECT DISTINCT ON</literal> eliminates rows that
131+
match on all the specified expressions. <literal>SELECT ALL</literal>
132+
(the default) will return all candidate rows, including
133+
duplicates. (See <xref linkend="sql-distinct"
134+
endterm="sql-distinct-title"> below.)
135+
</para>
136+
</listitem>
137+
127138
<listitem>
128139
<para>
129140
Using the operators <literal>UNION</literal>,
@@ -136,7 +147,11 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
136147
strictly in both result sets. The <literal>EXCEPT</literal>
137148
operator returns the rows that are in the first result set but
138149
not in the second. In all three cases, duplicate rows are
139-
eliminated unless <literal>ALL</literal> is specified. (See
150+
eliminated unless <literal>ALL</literal> is specified. The noise
151+
word <literal>DISTINCT</> can be added to explicitly specify
152+
eliminating duplicate rows. Notice that <literal>DISTINCT</> is
153+
the default behavior here, even though <literal>ALL</literal> is
154+
the default for <command>SELECT</> itself. (See
140155
<xref linkend="sql-union" endterm="sql-union-title">, <xref
141156
linkend="sql-intersect" endterm="sql-intersect-title">, and
142157
<xref linkend="sql-except" endterm="sql-except-title"> below.)
@@ -153,17 +168,6 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
153168
</para>
154169
</listitem>
155170

156-
<listitem>
157-
<para>
158-
<literal>DISTINCT</literal> eliminates duplicate rows from the
159-
result. <literal>DISTINCT ON</literal> eliminates rows that
160-
match on all the specified expressions. <literal>ALL</literal>
161-
(the default) will return all candidate rows, including
162-
duplicates. (See <xref linkend="sql-distinct"
163-
endterm="sql-distinct-title"> below.)
164-
</para>
165-
</listitem>
166-
167171
<listitem>
168172
<para>
169173
If the <literal>LIMIT</literal> (or <literal>FETCH FIRST</literal>) or <literal>OFFSET</literal>
@@ -219,7 +223,7 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
219223
subquery to reference itself by name. Such a subquery must have
220224
the form
221225
<synopsis>
222-
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL ] <replaceable class="parameter">recursive_term</replaceable>
226+
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
223227
</synopsis>
224228
where the recursive self-reference must appear on the right-hand
225229
side of the <literal>UNION</>. Only one recursive self-reference
@@ -755,13 +759,52 @@ UNBOUNDED FOLLOWING
755759
</para>
756760
</refsect2>
757761

762+
<refsect2 id="sql-distinct">
763+
<title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
764+
765+
<para>
766+
If <literal>SELECT DISTINCT</> is specified, all duplicate rows are
767+
removed from the result set (one row is kept from each group of
768+
duplicates). <literal>SELECT ALL</> specifies the opposite: all rows are
769+
kept; that is the default.
770+
</para>
771+
772+
<para>
773+
<literal>SELECT DISTINCT ON ( <replaceable
774+
class="parameter">expression</replaceable> [, ...] )</literal>
775+
keeps only the first row of each set of rows where the given
776+
expressions evaluate to equal. The <literal>DISTINCT ON</literal>
777+
expressions are interpreted using the same rules as for
778+
<literal>ORDER BY</> (see above). Note that the <quote>first
779+
row</quote> of each set is unpredictable unless <literal>ORDER
780+
BY</> is used to ensure that the desired row appears first. For
781+
example:
782+
<programlisting>
783+
SELECT DISTINCT ON (location) location, time, report
784+
FROM weather_reports
785+
ORDER BY location, time DESC;
786+
</programlisting>
787+
retrieves the most recent weather report for each location. But
788+
if we had not used <literal>ORDER BY</> to force descending order
789+
of time values for each location, we'd have gotten a report from
790+
an unpredictable time for each location.
791+
</para>
792+
793+
<para>
794+
The <literal>DISTINCT ON</> expression(s) must match the leftmost
795+
<literal>ORDER BY</> expression(s). The <literal>ORDER BY</> clause
796+
will normally contain additional expression(s) that determine the
797+
desired precedence of rows within each <literal>DISTINCT ON</> group.
798+
</para>
799+
</refsect2>
800+
758801
<refsect2 id="SQL-UNION">
759802
<title id="sql-union-title"><literal>UNION</literal> Clause</title>
760803

761804
<para>
762805
The <literal>UNION</literal> clause has this general form:
763806
<synopsis>
764-
<replaceable class="parameter">select_statement</replaceable> UNION [ ALL ] <replaceable class="parameter">select_statement</replaceable>
807+
<replaceable class="parameter">select_statement</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
765808
</synopsis>
766809
<replaceable class="parameter">select_statement</replaceable> is
767810
any <command>SELECT</command> statement without an <literal>ORDER
@@ -791,6 +834,8 @@ UNBOUNDED FOLLOWING
791834
<literal>ALL</> prevents elimination of duplicates. (Therefore,
792835
<literal>UNION ALL</> is usually significantly quicker than
793836
<literal>UNION</>; use <literal>ALL</> when you can.)
837+
<literal>DISTINCT</> can be written to explicitly specify the
838+
default behavior of eliminating duplicate rows.
794839
</para>
795840

796841
<para>
@@ -812,7 +857,7 @@ UNBOUNDED FOLLOWING
812857
<para>
813858
The <literal>INTERSECT</literal> clause has this general form:
814859
<synopsis>
815-
<replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
860+
<replaceable class="parameter">select_statement</replaceable> INTERSECT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
816861
</synopsis>
817862
<replaceable class="parameter">select_statement</replaceable> is
818863
any <command>SELECT</command> statement without an <literal>ORDER
@@ -833,6 +878,8 @@ UNBOUNDED FOLLOWING
833878
With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
834879
left table and <replaceable>n</> duplicates in the right table will appear
835880
min(<replaceable>m</>,<replaceable>n</>) times in the result set.
881+
<literal>DISTINCT</> can be written to explicitly specify the
882+
default behavior of eliminating duplicate rows.
836883
</para>
837884

838885
<para>
@@ -858,7 +905,7 @@ UNBOUNDED FOLLOWING
858905
<para>
859906
The <literal>EXCEPT</literal> clause has this general form:
860907
<synopsis>
861-
<replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL ] <replaceable class="parameter">select_statement</replaceable>
908+
<replaceable class="parameter">select_statement</replaceable> EXCEPT [ ALL | DISTINCT ] <replaceable class="parameter">select_statement</replaceable>
862909
</synopsis>
863910
<replaceable class="parameter">select_statement</replaceable> is
864911
any <command>SELECT</command> statement without an <literal>ORDER
@@ -878,6 +925,8 @@ UNBOUNDED FOLLOWING
878925
With <literal>ALL</>, a row that has <replaceable>m</> duplicates in the
879926
left table and <replaceable>n</> duplicates in the right table will appear
880927
max(<replaceable>m</>-<replaceable>n</>,0) times in the result set.
928+
<literal>DISTINCT</> can be written to explicitly specify the
929+
default behavior of eliminating duplicate rows.
881930
</para>
882931

883932
<para>
@@ -987,45 +1036,6 @@ SELECT name FROM distributors ORDER BY code;
9871036
</para>
9881037
</refsect2>
9891038

990-
<refsect2 id="sql-distinct">
991-
<title id="sql-distinct-title"><literal>DISTINCT</literal> Clause</title>
992-
993-
<para>
994-
If <literal>DISTINCT</> is specified, all duplicate rows are
995-
removed from the result set (one row is kept from each group of
996-
duplicates). <literal>ALL</> specifies the opposite: all rows are
997-
kept; that is the default.
998-
</para>
999-
1000-
<para>
1001-
<literal>DISTINCT ON ( <replaceable
1002-
class="parameter">expression</replaceable> [, ...] )</literal>
1003-
keeps only the first row of each set of rows where the given
1004-
expressions evaluate to equal. The <literal>DISTINCT ON</literal>
1005-
expressions are interpreted using the same rules as for
1006-
<literal>ORDER BY</> (see above). Note that the <quote>first
1007-
row</quote> of each set is unpredictable unless <literal>ORDER
1008-
BY</> is used to ensure that the desired row appears first. For
1009-
example:
1010-
<programlisting>
1011-
SELECT DISTINCT ON (location) location, time, report
1012-
FROM weather_reports
1013-
ORDER BY location, time DESC;
1014-
</programlisting>
1015-
retrieves the most recent weather report for each location. But
1016-
if we had not used <literal>ORDER BY</> to force descending order
1017-
of time values for each location, we'd have gotten a report from
1018-
an unpredictable time for each location.
1019-
</para>
1020-
1021-
<para>
1022-
The <literal>DISTINCT ON</> expression(s) must match the leftmost
1023-
<literal>ORDER BY</> expression(s). The <literal>ORDER BY</> clause
1024-
will normally contain additional expression(s) that determine the
1025-
desired precedence of rows within each <literal>DISTINCT ON</> group.
1026-
</para>
1027-
</refsect2>
1028-
10291039
<refsect2 id="SQL-LIMIT">
10301040
<title id="sql-limit-title"><literal>LIMIT</literal> Clause</title>
10311041

‎doc/src/sgml/ref/select_into.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
3030
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
3131
[ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
3232
[ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
33-
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
33+
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
3434
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
3535
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
3636
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]

0 commit comments

Comments
 (0)