Combining Queries: Union, INTERSECT, EXCEPT

The results of two queries can be combined using the set operations union, intersection, and difference: query1 UNION [ALL] query2 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2

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 ALL is specified.

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

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. Again, duplicates are eliminated unless ALL is specified.

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.

Set operations can be nested and chained:

query1 UNION query2 UNION query3 which really says (query1 UNION query2) UNION query3

Examples:

SELECT * FROM stud ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 1 | fred | 2 | bio | m 3 | tom | 1 | bio | m 4 | john | 3 | phy | m 2 | lisa | 2 | bio | f (4 rows) SELECT * FROM stud_extern ; id | name | semester | diploma | gender ----+--------+----------+---------+-------- 3 | tom | 1 | bio | m 4 | john | 3 | phy | m 5 | simone | 4 | inf | f (3 rows)
Union: SELECT * FROM stud UNION SELECT * FROM stud_extern ; id | name | semester | diploma | gender ----+--------+----------+---------+-------- 1 | fred | 2 | bio | m 2 | lisa | 2 | bio | f 3 | tom | 1 | bio | m 4 | john | 3 | phy | m 5 | simone | 4 | inf | f (5 rows)
INTERSECT: SELECT * FROM stud INTERSECT SELECT * FROM stud_extern ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 3 | tom | 1 | bio | m 4 | john | 3 | phy | m (2 rows)
EXCEPT: SELECT * FROM stud EXCEPT SELECT * FROM stud_extern ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 1 | fred | 2 | bio | m 2 | lisa | 2 | bio | f (2 rows)