Horton

Thread

Commits

  1. Doc: improve description of UNION/INTERSECT/EXCEPT syntax.

  1. clarification on chaining of set operations

    The Post Office <noreply@postgresql.org> — 2021-10-04T23:08:37Z

    The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/queries-union.html
Description:

Apologies if this has been raised previously - I searched the archives and
did not find anything.

Regarding section 7.4 of the documentation.  I was curious about the
behavior when combining different set operations in one query, e.g, 
query1 op1 query2 op2 query3;
where op1 and op2 are one of UNION, INTERSECT, EXCEPT.

The documentation suggests that this is equivalent to 
(query1 op1 query2) op2 query3;
but only states it for the case when op1 = op2 = UNION.  (Which really
doesn't matter anyway given that set union is associative.)

In fact, mixing INTERSECT and UNION does not exhibit the expected behavior
(at least in version 12 - I have not tested in later versions).  Here's my
counterexample:

CREATE TABLE test1 (x INTEGER);
CREATE TABLE test2 (x INTEGER);
CREATE TABLE test3 (x INTEGER);

INSERT INTO test1 VALUES (1), (2);
INSERT INTO test2 VALUES (2), (3);
INSERT INTO test3 VALUES (3), (4);

SELECT * FROM test1
UNION
SELECT * FROM test2
INTERSECT
SELECT * FROM test3;

(SELECT * FROM test1
UNION
SELECT * FROM test2)
INTERSECT
SELECT * FROM test3;

The observed behavior suggests that INTERSECT is applied before UNION.  I
haven't tested to figure out other interactions (e.g., with EXCEPT or the
ALL variations).

  2. Re: clarification on chaining of set operations

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-10-04T23:40:38Z

    PG Doc comments form <noreply@postgresql.org> writes:
> Regarding section 7.4 of the documentation.  I was curious about the
> behavior when combining different set operations in one query, e.g, 
> query1 op1 query2 op2 query3;
> where op1 and op2 are one of UNION, INTERSECT, EXCEPT.

> The documentation suggests that this is equivalent to 
> (query1 op1 query2) op2 query3;
> but only states it for the case when op1 = op2 = UNION.

The SELECT reference page explains that INTERSECT binds more tightly
than UNION or EXCEPT.  I think it's an oversight that section 7.4
doesn't mention that.

			regards, tom lane