pg.ddx.io pgsql-docs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
clarification on chaining of set operations 2+ messages / 2 participants [nested] [flat]
* clarification on chaining of set operations @ 2021-10-04 23:08 PG Doc comments form <noreply@postgresql.org> 2021-10-04 23:40 ` Re: clarification on chaining of set operations Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: PG Doc comments form @ 2021-10-04 23:08 UTC (permalink / raw) To: pgsql-docs@lists.postgresql.org; +Cc: cpainterwakefield@gmail.com 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). ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: clarification on chaining of set operations 2021-10-04 23:08 clarification on chaining of set operations PG Doc comments form <noreply@postgresql.org> @ 2021-10-04 23:40 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2021-10-04 23:40 UTC (permalink / raw) To: cpainterwakefield@gmail.com; +Cc: pgsql-docs@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-10-04 23:40 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-10-04 23:08 clarification on chaining of set operations PG Doc comments form <noreply@postgresql.org> 2021-10-04 23:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox