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.
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 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 3 abc 5 xyz SELECT * FROM t2; I2 C2 ---------- --- 1 abc abc 3 4 xyz 4 abc
(SELECT * from T1) UNION ALL (SELECT * FROM T2); I1 C1 ---------- --- 1 abc 2 def 3 abc 3 abc 5 xyz 1 abc 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 5 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