Collection SELECT

This topic describes the syntax of collection SELECT.

Purpose

This statement is used to merge the results of multiple SELECT queries into one result. Each SELECT query must have the same number of columns and data types, but the column lengths can be different.

Syntax

select_clause_set:
  simple_select [ UNION | UNION ALL | MINUS | INTERSECT] select_clause_set_right
      [ORDER BY sort_list_columns]

select_clause_set_right:
    simple_select
    | select_caluse_set

Parameters

Examples

Create sample tables tbl1 and tbl2.

obclient> CREATE TABLE tbl1 (col1 INT, col2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1, 1), (2, 2),(2,2);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> INSERT INTO tbl2 VALUES (2, 2),(2, 2),(3,3);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+
| COL1 | COL2 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
+------+------+
3 rows in set

obclient> SELECT * FROM tbl2;
+------+------+
| COL1 | COL2 |
+------+------+
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set

• Calculate all records in tbl1 and tbl2.

  obclient> SELECT col1, col2 FROM tbl1 UNION ALL SELECT col1, col2 FROM tbl2;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 |    1 |
  |    2 |    2 |
  |    2 |    2 |
  |    2 |    2 |
  |    2 |    2 |
  |    3 |    3 |
  +------+------+
  6 rows in set
  

• Calculate all unique records in tbl1 and tbl2.

  obclient> SELECT col1, col2 FROM tbl1 UNION SELECT col1, col2 FROM tbl2;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 |    1 |
  |    2 |    2 |
  |    3 |    3 |
  +------+------+
  3 rows in set
  

• Calculate the common data in tables tbl1 and tbl2.

  obclient> SELECT col1, col2 FROM tbl1 INTERSECT SELECT col1, col2 FROM tbl2;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    2 |    2 |
  +------+------+
  1 row in set
  

• Calculate the data that exists in table tbl1 but not in table tbl2.

  obclient> SELECT col1, col2 FROM tbl1 MINUS SELECT col1, col2 FROM tbl2;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 |    1 |
  +------+------+
  1 row in set