diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2021-10-05 10:24:14 -0400 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2021-10-05 10:24:14 -0400 |
| commit | f3fec23dbdead113700fb1b401b681fa24f1e4f4 (patch) | |
| tree | 205cba870d8107fa7ef9916cf2acdc044f784b22 /doc/src | |
| parent | f6b5d05ba9a4ac7c5ebec76045c6e0afcf7c9eec (diff) | |
| download | postgresql-f3fec23dbdead113700fb1b401b681fa24f1e4f4.tar.gz postgresql-f3fec23dbdead113700fb1b401b681fa24f1e4f4.zip | |
Doc: improve description of UNION/INTERSECT/EXCEPT syntax.
queries.sgml failed to mention the rather important point that
INTERSECT binds more tightly than UNION or EXCEPT. I thought
it could also use more discussion of the role of parentheses
in these constructs.
Per gripe from Christopher Painter-Wakefield.
Discussion: https://postgr.es/m/163338891727.12510.3939775743980651160@wrigleys.postgresql.org
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/queries.sgml | 53 |
1 files changed, 43 insertions, 10 deletions
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 834b83b5098..516fbcbf375 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1707,17 +1707,9 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable> <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable> </synopsis> - <replaceable>query1</replaceable> and + where <replaceable>query1</replaceable> and <replaceable>query2</replaceable> are queries that can use any of - the features discussed up to this point. Set operations can also - be nested and chained, for example -<synopsis> -<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> UNION <replaceable>query3</replaceable> -</synopsis> - which is executed as: -<synopsis> -(<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) UNION <replaceable>query3</replaceable> -</synopsis> + the features discussed up to this point. </para> <para> @@ -1751,6 +1743,47 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab the corresponding columns have compatible data types, as described in <xref linkend="typeconv-union-case"/>. </para> + + <para> + Set operations can be combined, for example +<synopsis> +<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> EXCEPT <replaceable>query3</replaceable> +</synopsis> + which is equivalent to +<synopsis> +(<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) EXCEPT <replaceable>query3</replaceable> +</synopsis> + As shown here, you can use parentheses to control the order of + evaluation. Without parentheses, <literal>UNION</literal> + and <literal>EXCEPT</literal> associate left-to-right, + but <literal>INTERSECT</literal> binds more tightly than those two + operators. Thus +<synopsis> +<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable> +</synopsis> + means +<synopsis> +<replaceable>query1</replaceable> UNION (<replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>) +</synopsis> + You can also surround an individual <replaceable>query</replaceable> + with parentheses. This is important if + the <replaceable>query</replaceable> needs to use any of the clauses + discussed in following sections, such as <literal>LIMIT</literal>. + Without parentheses, you'll get a syntax error, or else the clause will + be understood as applying to the output of the set operation rather + than one of its inputs. For example, +<synopsis> +SELECT a FROM b UNION SELECT x FROM y LIMIT 10 +</synopsis> + is accepted, but it means +<synopsis> +(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10 +</synopsis> + not +<synopsis> +SELECT a FROM b UNION (SELECT x FROM y LIMIT 10) +</synopsis> + </para> </sect1> |