Table Operators - UNION [ALL], INTERSECT, MINUS

Purpose

To combine the results of various queries with one another, the table operators UNION ALL, UNION, INTERSECT, and MINUS (=EXCEPT) exist. These calculate the set union, the set union without duplicates, the intersection without duplicates, and the set difference without duplicates from two subqueries.

Table Operator Meaning
UNION ALL

Union from both subqueries. All of the rows from both operands are taken into account.
UNION

The set union from both subqueries without duplicates. All of the rows from both operands are taken into account. Duplicate entries in the result are eliminated.
INTERSECT

The intersection from both subqueries without duplicates. All of the rows that appear in both operands are accounted for in the result. Duplicate entries in the result are eliminated.
MINUS or EXCEPT

The set difference from both subqueries without duplicates. The result comprises those rows in the left operand that do not exist in the right operand. Duplicate entries in the result are eliminated.

Syntax

Table Operator

Usage Notes

  • The table operators (except UNION ALL) are expensive operations and can lead to performance problems, in particular with very large tables. This is primarily because the result must not contain duplicates. Removing duplicates is an expensive operation.
  • The number of columns of both operands must match, and the data types of the columns of both operands must be compatible.
  • The names of the left operand are used as columns name for the result.
  • Additionally, several table operators can be combined next to one another. In this respect, INTERSECT has higher priority than UNION [ALL] and MINUS. Within UNION [ALL] and MINUS evaluation is performed from left to right. However, for reasons of clarity, parentheses should always be used.
  • EXCEPT comes from the SQL standard, MINUS is an alias and is, for example, supported by Oracle. Exasol supports both alternatives.

Examples

The following examples relate to these tables:

SELECT * FROM t1;
I1 C1
1 abc
2 def
3 abc
4 abc
5 xyz 
SELECT * FROM t2;
I2 C2
1 abc
  abc
3  
4 xyz
5 abc 
(SELECT * from T1) UNION ALL (SELECT * FROM T2);
I1 C1
1 abc
2 def
3 abc
3 abc
5 xyz
  abc
3  
4 xyz
4 abc 
(SELECT * from T1) UNION (SELECT * FROM T2);
I1 C1
1 abc
3 abc
4 abc
  abc
2 def
4 xyz
3   
(SELECT * from T1) INTERSECT (SELECT * FROM T2);
I1 C1
1 abc 
(SELECT * from T1) MINUS (SELECT * FROM T2);
I1 C1
3 abc
2 def
5 xyz