Set operations are used to combine the results of several queries. It is different from joining, although the same results often can be achieved by joining. Simply speaking, joining means placing the records of two tables besides each other horizontally. The result of joining is that the number of columns equals to the sum of the numbers of columns of the source tables, and the number of records will depend on the join conditions.

Combining, in contrast, means putting the result of one query on top of the result of another query. The number of columns stays the same, but the number of rows is the sum of the rows from the sources.

There are three set operations:

• UNION: This appends the result of one query to the result of another query
• INTERSECT: This returns the records that exist in the results of both queries
• EXCEPT: This returns the records from the first query that does not exist in the result of the second query--the difference

The syntax of set operations is as follows:

<query1> UNION <query2>;

<query1> INTERSECT <query2>;

<query1> EXCEPT <query2>;

It is possible to use several set operations in one statement:

SELECT a, b FROM t1
UNION
SELECT c, d FROM t2
INTERSECT
SELECT e, f FROM t3;

The priority of all set operations is the same. This means that logically they are executed in the same order as used in the code. However, the records can be returned in a different order that is not predicted, unless the ORDER BY clause is used. In this case, the ORDER BY clause is applied after all of the set operations. For this reason, it does not make sense to put ORDER BY into the subqueries.

All set operations by default remove duplicated records as if SELECT DISTINCT is used. To avoid this and return all the records, the ALL keyword should be used, which is specified after the name of the set operation:

<query1> UNION ALL <query2>.

The set operations can be used to find the difference between two tables:

car_portal=> SELECT 'a', * FROM
(
  SELECT * FROM car_portal_app.a
  EXCEPT ALL
  SELECT * FROM car_portal_app.b
) v1
UNION ALL
SELECT 'b', * FROM
(
  SELECT * FROM car_portal_app.b
  EXCEPT ALL
  SELECT * FROM car_portal_app.a
) v2;
 ?column? | a_int | a_text
----------+-------+--------
 a        |     1 | one
 b        |     4 | four
(2 rows)

From the results of that query, you can find out that row one exists in the table a but does not exist in the table b. Row four exists in the table b, but not in a.

It is possible to append one set of records to another only when they have the same number of columns and they have respectively the same data types, or compatible data types. The output names for the columns are always taken from the first subquery, even if they are different in subsequent queries.