UNION

Description

UNION combines the result from multiple SELECT statements into a single result set.

Syntax

  1. SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]

Arguments

UNION [DISTINCT] and UNION ALL

By default, duplicate rows are removed from the UNION results. UNION is used as the same as UNION [DISTINCT].

Combining the result sets of two or more queries using the UNION operator requires the following conditions:

  • All SELECT statements must have the same number and order of columns.

  • Data types must be same or convertible.

With UNION ALL, repeated lines (if available) are retained in the result.

ORDER BY and LIMIT In UNION

To apply anORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses:

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, ORDER BY in this context typically is used in conjunction with LIMIT, to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result.

For example:

  1. (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
  2. UNION
  3. (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Or:

  1. (SELECT a FROM t1 WHERE a=10 AND B=1)
  2. UNION
  3. (SELECT a FROM t2 WHERE a=11 AND B=2)
  4. ORDER BY a LIMIT 10;

Examples

  1. CREATE TABLE t1 (id INT PRIMARY KEY);
  2. CREATE TABLE t2 (id INT PRIMARY KEY);
  3. INSERT INTO t1 VALUES (1),(2),(3);
  4. INSERT INTO t2 VALUES (2),(3),(4);
  6. mysql> SELECT id FROM t1 UNION SELECT id FROM t2;
  7. +------+
  8. | id   |
  9. +------+
  10. |    4 |
  11. |    1 |
  12. |    2 |
  13. |    3 |
  14. +------+
  16. mysql> SELECT id FROM t1 UNION ALL SELECT id FROM t2;
  17. +------+
  18. | id   |
  19. +------+
  20. |    1 |
  21. |    2 |
  22. |    3 |
  23. |    2 |
  24. |    3 |
  25. |    4 |
  26. +------+
  1. drop table t1;
  2. CREATE TABLE t1 (a INT, b INT);
  3. INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9),ROW(10,1),ROW(11,2),ROW(13,5);
  4. drop table t2;
  5. CREATE TABLE t2 (a INT, b INT);
  6. INSERT INTO t2 VALUES ROW(1,2),ROW(3,4),ROW(11,2),ROW(10,3),ROW(15,8);
  8. mysql> (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
  9. +------+
  10. | a    |
  11. +------+
  12. |   10 |
  13. |   11 |
  14. +------+