June 27th, 2024:
PostgreSQL 17 Beta 2 Released!

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

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

UNION [ALL]`query1`

`query2`

INTERSECT [ALL]`query1`

`query2`

EXCEPT [ALL]`query1`

`query2`

where * query1* and

`query2`

`UNION`

effectively appends the result of * query2* to the result of

`query1`

`DISTINCT`

, unless `UNION ALL`

is used.`INTERSECT`

returns all rows that are both in the result of * query1* and in the result of

`query2`

`INTERSECT ALL`

is used.`EXCEPT`

returns all rows that are in the result of * query1* but not in the result of

`query2`

`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 return the same number of columns and the corresponding columns have compatible data types, as described in Section 10.5.

Set operations can be combined, for example

UNION`query1`

EXCEPT`query2`

`query3`

which is equivalent to

(UNION`query1`

) EXCEPT`query2`

`query3`

As shown here, you can use parentheses to control the order of evaluation. Without parentheses, `UNION`

and `EXCEPT`

associate left-to-right, but `INTERSECT`

binds more tightly than those two operators. Thus

UNION`query1`

INTERSECT`query2`

`query3`

means

UNION (`query1`

INTERSECT`query2`

)`query3`

You can also surround an individual * query* with parentheses. This is important if the

`query`

`LIMIT`

. 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,SELECT a FROM b UNION SELECT x FROM y LIMIT 10

is accepted, but it means

(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

not

SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)