Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 7.4

PostgreSQL 7.4.16 Documentation
Prev	Fast Backward	Chapter 7. Queries	Fast Forward	Next

7.4. Combining Queries

The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

query1 and query2 are queries that can use any of the features discussed up to this point. Set operations can also be nested and chained, for example

query1 UNION query2 UNION query3

which really says

(query1 UNION query2) UNION query3

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates all duplicate rows, in the sense of DISTINCT, unless UNION ALL is used.

INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used.

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used.

In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they both return the same number of columns, and that the corresponding columns have compatible data types, as described in Section 10.5.

Prev	Home	Next
Select Lists	Up	Sorting Rows

User Comments

Mark Krenz <mark AT slugbug.org>

05 Jul 2004 14:59:30

It\'s important to use the ALL keyword in cases where you are expecting to agregate two different sets of columns together with some function. For instance, I found that I needed to use it when I was trying to sum the amount columns in two tables, invoice_items and payments, so that I could get the total balance. When I didn\'t use the ALL keyword, my balance was off by however many duplicate rows had been eaten.

Add Comment

Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.

In order to submit a comment, you must have a community account.

* denotes required field