Collection operations

In seekdb, you can use the collection operators UNION, UNION ALL, INTERSECT, and MINUS | EXCEPT to combine multiple queries. These collection operators have the same precedence. If an SQL statement contains multiple collection operators, seekdb evaluates them from left to right, unless parentheses are used to specify the order of operations.

This topic describes how to use the collection operators UNION, UNION ALL, INTERSECT, and MINUS.

Syntax

select_clause_set_left 
{ UNION | UNION ALL | MINUS | INTERSECT}
select_clause_set_right

For more information about collection query syntax, see UNION clause.

Parameter description:

• select_clause_set_left: a SELECT statement that returns the left set for the collection operation.
• select_clause_set_right: a SELECT statement that returns the right set for the collection operation.
• UNION: combines the result sets of two or more SELECT statements into a single set and removes duplicate rows.
• UNION ALL: combines the result sets of two or more SELECT statements into a single set without removing duplicate rows.
• INTERSECT: returns the intersection of the result sets of two SELECT statements.
• MINUS | EXCEPT: returns the rows in the result set of the first SELECT statement that are not present in the result set of the second SELECT statement. MINUS is a synonym for EXCEPT.

tip

Each collection operator operates on the result sets of SELECT statements, and the result sets must have the same number of columns and data types.

Examples

Assume that the test_tbl1 and test_tbl2 tables have the following data:

 SELECT * FROM test_tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
+------+------+
6 rows in set

 SELECT * FROM test_tbl2;
+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
6 rows in set

UNION example

The following SQL statement uses UNION to combine the result sets of two SELECT statements into a single set, and the combined result set removes duplicate rows.

SELECT id, name FROM test_tbl1
UNION
SELECT id, name FROM test_tbl2;

The result is as follows:

+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
9 rows in set

UNION ALL example

The following SQL statement uses UNION ALL to combine the result sets of two SELECT statements into a single set, and the combined result set does not remove duplicate rows.

SELECT id, name FROM test_tbl1
UNION ALL
SELECT id, name FROM test_tbl2;

The result is as follows:

+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
|    7 | A7   |
|    8 | A8   |
|    9 | A9   |
+------+------+
12 rows in set

INTERSECT example

The following SQL statement uses INTERSECT to return the intersection of the result sets of two SELECT statements.

SELECT id, name FROM test_tbl1
INTERSECT
SELECT id, name FROM test_tbl2;

The result is as follows:

+------+------+
| id   | name |
+------+------+
|    1 | A1   |
|    2 | A2   |
|    3 | A3   |
+------+------+
3 rows in set

MINUS example

The following SQL statement uses MINUS or EXCEPT to return the rows in the result set of the first SELECT statement that are not present in the result set of the second SELECT statement.

SELECT id, name FROM test_tbl1
MINUS
SELECT id, name FROM test_tbl2;

or

SELECT id, name FROM test_tbl1
EXCEPT
SELECT id, name FROM test_tbl2;

The result is as follows:

+------+------+
| id   | name |
+------+------+
|    4 | A4   |
|    5 | A5   |
|    6 | A6   |
+------+------+
3 rows in set

References

UNION clause