UNION clause

Description

This statement is used to perform set operations on the results of multiple SELECT queries.

Limitations and considerations

• Set operators have the same precedence. If an SQL statement contains multiple set operators, seekdb calculates them from left to right unless parentheses are used to specify the order of operations.

• Each set operator can only operate on the result sets of SELECT statements, and the number of columns and data types in each result set must be the same.

Syntax

select_stmt
    {UNION | UNION ALL | MINUS | EXCEPT | INTERSECT} select_stmt
    [, {UNION | UNION ALL | MINUS | EXCEPT | INTERSECT} select_stmt ...]
    [ORDER BY order_by_condition_list]
    [LIMIT limit_clause];

Parameters

Parameter	Description
select_stmt	The SELECT statement to be used for set operations. For more information about the SELECT statement, see SELECT.
UNION	Merges the result sets of two SELECT statements into one set and removes duplicate rows.
UNION ALL	Merges the result sets of two SELECT statements into one set without removing duplicate rows.
MINUS | EXCEPT	Returns rows from the result set of the preceding SELECT statement that are not present in the result set of the following SELECT statement. MINUS is a synonym for EXCEPT.
INTERSECT	Returns the intersection of the result sets of two SELECT statements and removes duplicates.
ORDER BY order_by_condition_list	Specifies the sorting after set operations.
LIMIT limit_clause	Specifies the number of rows to return after set operations.

Examples

1. Create tables test_tbl1 and test_tbl2.

   CREATE TABLE test_tbl1 (col1 INT, col2 INT);
   CREATE TABLE test_tbl2 (col1 INT, col2 INT);

2. Insert test data into tables test_tbl1 and test_tbl2.

   INSERT INTO test_tbl1 VALUES (1, 1), (2, 2), (4, 4);
   INSERT INTO test_tbl2 VALUES (2, 2), (3, 3), (5, 5);

• Select the col1 and col2 columns from tables test_tbl1 and test_tbl2, and merge them into one result set using the UNION ALL operator, retaining duplicate rows.

  SELECT col1, col2 FROM test_tbl1
  UNION ALL
  SELECT col1, col2 FROM test_tbl2;

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 |    1 |
  |    2 |    2 |
  |    4 |    4 |
  |    2 |    2 |
  |    3 |    3 |
  |    5 |    5 |
  +------+------+
  6 rows in set (0.023 sec)

• Select the col1 and col2 columns from tables test_tbl1 and test_tbl2, and merge them into one result set using the UNION operator, removing duplicate rows.

  SELECT col1, col2 FROM test_tbl1
  UNION
  SELECT col1, col2 FROM test_tbl2;

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 |    1 |
  |    2 |    2 |
  |    4 |    4 |
  |    3 |    3 |
  |    5 |    5 |
  +------+------+
  5 rows in set (0.021 sec)

• Select the col1 and col2 columns from tables test_tbl1 and test_tbl2, and return the intersection of the two tables. That is, return the duplicate rows in the col1 and col2 columns of test_tbl1 and test_tbl2.

  SELECT col1, col2 FROM test_tbl1
  INTERSECT
  SELECT col1, col2 FROM test_tbl2;

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    2 |    2 |
  +------+------+
  1 row in set (0.013 sec)

• Select the col1 and col2 columns from table test_tbl1, and exclude the same col1 and col2 columns from table test_tbl2. That is, return the rows in test_tbl1 that are not present in test_tbl2.

  SELECT col1, col2 FROM test_tbl1
  EXCEPT
  SELECT col1, col2 FROM test_tbl2;

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 |    1 |
  |    4 |    4 |
  +------+------+
  2 rows in set (0.011 sec)

• Select the col1 and col2 columns from tables test_tbl1 and test_tbl2, and merge them into one result set. Then, sort the merged result set in descending order by col1 and return only the top 3 results.

  SELECT col1, col2 FROM test_tbl1
  UNION
  SELECT col1, col2 FROM test_tbl2
  ORDER BY col1 DESC
  LIMIT 3;

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    5 |    5 |
  |    4 |    4 |
  |    3 |    3 |
  +------+------+
  3 rows in set (0.012 sec)

References

• SELECT
• Set operations