Set Operators

Set operators allow queries to be combined.

In this Topic:

General Syntax

[ ( ] <query> [ ) ] { INTERSECT | { MINUS | EXCEPT } | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]

General Usage Notes

  • Each query can itself contain query operators to allow any number of query expressions to be combined with set operators.

  • The ORDER BY and LIMIT / FETCH clauses are applied to the result of the set operator.

  • When using these operators:

    • Make sure that each query selects the same number of columns.

    • Make sure that the data type of each column is consistent across the rows from different sources. One of the examples in the Examples section below illustrates the potential problem and solution when data types do not match.

    • In general, the “meanings”, as well as the data types, of the columns should match. The following will not produce the desired results:

      SELECT LastName, FirstName FROM employees
      UNION ALL
      SELECT FirstName, LastName FROM contractors;
      

      The risk of error increases when using the asterisk to specify all columns of a table, for example:

      SELECT * FROM table1
      UNION ALL
      SELECT * FROM table2;
      

      If the tables have the same number of columns, but the columns are not in the same order, the query results will probably be incorrect.

    • The names of the output columns are based on the names of the columns of the first query. For example, the following query:

      SELECT LastName, FirstName FROM employees
      UNION ALL
      SELECT FirstName, LastName FROM contractors;
      

      behaves as though the query were:

      SELECT LastName, FirstName FROM employees
      UNION ALL
      SELECT FirstName AS LastName, LastName AS FirstName FROM contractors;
      

INTERSECT

Returns rows from one query’s result set which also appear in another query’s result set, with duplicate elimination.

Syntax

SELECT ...
INTERSECT
SELECT ...

MINUS , EXCEPT

Removes rows from one query’s result set which appear in another query’s result set, with duplicate elimination.

The MINUS and EXCEPT keywords have the same meaning and can be used interchangeably.

Syntax

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...

UNION [ ALL ]

Combines the result sets from two queries:

  • UNION combines with duplicate elimination.
  • UNION ALL combines without duplicate elimination.

The default is UNION (i.e. duplicate elimination).

Syntax

SELECT ...
UNION [ ALL ]
SELECT ...

Examples

This example demonstrates the basic usage of the UNION operator. It also demonstrates a potential issue when data types do not match, then provides the solution.

Start by creating the tables and inserting some data:

CREATE TABLE t1 (v VARCHAR);
CREATE TABLE t2 (i INTEGER);
INSERT INTO t1 (v) VALUES ('Adams, Douglas');
INSERT INTO t2 (i) VALUES (42);

Execute a UNION operation with different data types:

SELECT v FROM t1    -- VARCHAR
UNION
SELECT i FROM t2    -- INTEGER
;

Output:

100038 (22018): Numeric value 'Adams, Douglas' is not recognized

Now use explicit casting to convert the inputs to a compatible type:

SELECT v::VARCHAR FROM t1
UNION
SELECT i::VARCHAR FROM t2;

Output:

+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+