Table Operators - UNION [ALL], INTERSECT, MINUS

Purpose

To combine the results of various queries with one another, you can use table operators.

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

  • 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, and removing duplicates is expensive.
  • 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.
  • Several table operators can be combined. In this respect, INTERSECT has higher priority than UNION [ALL] and MINUS. Within UNION [ALL] and MINUS, evaluation is performed from left to right. We recommend using parentheses for clarity even when not required.
  • The keyword EXCEPT comes from the SQL standard, MINUS is an alias. Exasol supports both alternatives.

Examples

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