Set-based SELECT

This topic describes set-based SELECT statements, which are statements that use set operators.

Purpose

You can use such a statement to combine the results of multiple SELECT queries into one result. The SELECT queries must have the same number of columns and the same data type, but can have different column lengths.

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_clause_set

Parameters

Examples

Create two tables respectively named 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

• Combine all rows of the tbl1 and tbl2 tables, including duplicates.

  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
  

• Combine all unique rows of the tbl1 and tbl2 tables.

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

• Return rows that exist in both the tbl1 and tbl2 tables.

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

• Return rows that exist in the tbl1 table but do not exist in the tbl2 table.

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