UNION, INTERSECT, EXCEPT ​

These set operators compare and merge the results of two separate query expressions. You can select compatible column information from two separate tables, finding only the common rows (INTERSECT), rows that are in one result set but not the other (EXCEPT or MINUS), or a complete UNION of all of the rows from both sides.

query
 { UNION | INTERSECT | { EXCEPT | MINUS } } [ ALL | DISTINCT ] 
query

Each query must have the same number of columns, and the columns within each query must have compatible data types. You can write queries that contain multiple set operators connecting multiple query expressions.

You can use the ALL and DISTINCT keywords to return or discard any duplicate rows. The default behavior is to discard duplicates (DISTINCT).

To qualify for the EXCEPT or MINUS result set, rows must exist in the result of the first query expression but not in the result of the second. EXCEPT and MINUS are synonyms.

For example:

premdb=# select count(*) from 
(select htid, atid from team except select htid, atid from match) exc;
 count 
-------
   50
(1 row)

premdb=# select count(*) from 
(select htid, atid from match minus select htid, atid from team) exc;
 count 
-------
  1562
(1 row)

Parent topic:SELECT