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
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 thanUNION [ALL]
andMINUS
. WithinUNION [ALL]
andMINUS
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:
I1  C1 

1  abc 
2  def 
3  abc 
4  abc 
5  xyz 
I2  C2 

1  abc 
abc  
3  
4  xyz 
5  abc 
I1  C1 

1  abc 
2  def 
3  abc 
3  abc 
5  xyz 
abc  
3  
4  xyz 
4  abc 
I1  C1 

1  abc 
3  abc 
4  abc 
abc  
2  def 
4  xyz 
3 
I1  C1 

1  abc 
I1  C1 

3  abc 
2  def 
5  xyz 