MySQL

MySQL SQL Syntax

Using UNION

Retrieving from Multiple Tables with UNION

If you want to create a result set by selecting records from multiple tables one after the other, you can do that using a UNION statement. UNION is available as of MySQL 4, although prior to that you can use a couple of workarounds (shown later).

For the following examples, assume you have three tables, table1, table2, and t3 that look like this:

mysql> SELECT * FROM table1;

+------+-------+
| id   | value |
+------+-------+
|    1 | red   |
|    2 | blue  |
|    3 | green |
+------+-------+

mysql> SELECT * FROM table2;

+------+------+
| id   | value|
+------+------+
|   -1 | tan  |
|    1 | red  |
+------+------+

mysql> SELECT * FROM t3;

+------------+------+
| date       | id   |
+------------+------+
| 1904-01-01 |  100 |
| 2004-01-01 |  200 |
| 2004-01-01 |  200 |
+------------+------+

Tables' table1 and table2 have integer and character columns, and t3 has date and integer columns. To write a UNION statement that combines multiple retrievals, just write several SELECT statements and put the keyword UNION between them.

For example, to select the integer column from each table, do this:

mysql> SELECT id FROM table1 UNION SELECT id FROM table2 UNION SELECT id FROM t3;

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   -1 |
|  100 |
|  200 |
+------+

UNION has the following properties:

. The names and data types for the columns of the UNION result come from the names and types of the columns in the first SELECT. The second and subsequent SELECT statements in the UNION must select the same number of columns, but they need not have the same names or types.

Columns are matched by position (not by name), which is why these two queries return different results:

mysql> SELECT id, value FROM table1 UNION SELECT id, date FROM t3;

+------+------------+
| id   | value      |
+------+------------+
|    1 | red        |
|    2 | blue       |
|    3 | green      |
|  100 | 1904-01-01 |
|  200 | 2004-01-01 |
+------+------------+

mysql> SELECT id, value FROM table1 UNION SELECT date, id FROM t3;

+------+-------+
| id   | value |
+------+-------+
|    1 | red   |
|    2 | blue  |
|    3 | green |
| 1904 | 100   |
| 2004 | 200   |
+------+-------+

In both cases, the columns selected from table1 (i and c) determine the types used in the UNION result. These columns have integer and string types, so type conversion takes place when selecting values from t3. For the first query, d is converted from date to string. That happens to result in no loss of information. For the second query, d is converted from date to integer (which does lose information), and i is converted from integer to string.

By default, UNION eliminates duplicate rows from the result set:

mysql> SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM t3;

+------+-------+
| id   |value  |
+------+-------+
|    1 | red   |
|    2 | blue  |
|    3 | green |
|   -1 | tan   |
| 1904 | 100   |
| 2004 | 200   |
+------+-------+

table1 and table2 both have a row containing values of 1 and 'red', but only one such row appears in the output. Also, t3 has two rows containing '2004-01-01' and 200, one of which has been eliminated.

If you want to preserve duplicates, follow the first UNION keyword with ALL:

mysql> SELECT * FROM table1 UNION ALL SELECT * FROM table2 UNION SELECT * FROM t3;

+------+-------+
| id    | value|
+------+-------+
|    1 | red   |
|    2 | blue  |
|    3 | green |
|   -1 | tan   |
|    1 | red   |
| 1904 | 100   |
| 2004 | 200   |
| 2004 | 200   |
+------+-------+

. To sort a UNION result, add an ORDER BY clause after the last SELECT; it applies to the query result as a whole. However, because the UNION uses column names from the first SELECT, the ORDER BY should refer to those names, not the column names from the last SELECT, if they differ.

mysql> SELECT i, c FROM table1 UNION SELECT i, d FROM t3
-> ORDER BY c;

+------+------------+
| id   | value      |
+------+------------+
|  100 | 1904-01-01 |
|  200 | 2004-01-01 |
|    2 | blue       |
|    3 | green      |
|    1 | red        |
+------+------------+

You can also specify an ORDER BY clause for an individual SELECT statement within the UNION.

To do this, enclose the SELECT (including its ORDER BY) within parentheses:

mysql> (SELECT id, value FROM table1 ORDER BY id DESC)
-> UNION (SELECT id, value FROM table2 ORDER BY id);

+------+-------+
| id   | value |
+------+-------+
|    3 | green |
|    2 | blue  |
|    1 | red   |
|   -1 | tan   |
+------+-------+

. LIMIT can be used in a UNION in a manner similar to that for ORDER BY.

If added to the end of the statement, it applies to the UNION result as a whole:

mysql> SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM t3
-> LIMIT 1;

+------+------+
| id   | value|
+------+------+
|    1 | red  |
+------+------+

If enclosed within parentheses as part of an individual SELECT statement, it applies only to that SELECT:

mysql> (SELECT * FROM table1 LIMIT 1)
-> UNION (SELECT * FROM table2 LIMIT 1)
-> UNION (SELECT * FROM t3 LIMIT 1);

+------+------+
| id   | value|
+------+------+
|    1 | red  |
|   -1 | tan  |
| 1904 | 100  |
+------+------+

. You need not select from different tables. You can select different subsets of the same table using different conditions. This can be useful as an alternative to running several different SELECT queries, because you get all the rows in a single result set rather than as several result sets.

Prior to MySQL 4, UNION is unavailable, but you can work around this difficulty by selecting rows from each table into a temporary table and then selecting the contents of that table. In MySQL 3.23 and later, you can handle this problem easily by allowing the server to create the holding table for you. Also, you can make the table a temporary table so that it will be dropped automatically when your session with the server terminates. For quicker performance, use a HEAP (in-memory) table.

CREATE TEMPORARY TABLE tmp TYPE = HEAP SELECT ... FROM table1 WHERE ... ;
INSERT INTO tmp SELECT ... FROM table2 WHERE ... ;
INSERT INTO tmp SELECT ... FROM t3 WHERE ... ;

SELECT * FROM tmp ORDER BY ... ;

Because tmp is a TEMPORARY table, the server will drop it automatically when your client session ends. (Of course, you can drop the table explicitly as soon as you're done with it to allow the server to free resources associated with it. This is a good idea if you will continue to perform further queries, particularly for HEAP tables.)

For versions of MySQL older than 3.23, the concept is similar, but the details differ because the HEAP table type and TEMPORARY tables are unavailable, as is CREATE TABLE ... SELECT. To adapt the preceding procedure, it's necessary to explicitly create the table first before retrieving any rows into it. (The only table type available will be ISAM, so you cannot use a TYPE option.) Then retrieve the records into the table. When you're done with it, you must use DROP TABLE explicitly because the server will not drop it automatically.

CREATE TABLE tmp (column1, column2, ...);
INSERT INTO tmp SELECT ... FROM table1 WHERE ... ;
INSERT INTO tmp SELECT ... FROM table2 WHERE ... ;
INSERT INTO tmp SELECT ... FROM t3 WHERE ... ;
SELECT * FROM tmp ORDER BY ... ;
DROP TABLE tmp;

If you want to run a UNION-type query on MyISAM tables that have identical structure, you may be able to set up a MERGE table and query that as a workaround for lack of UNION. (In fact, this can be useful even if you do have UNION, because a query on a MERGE table will be simpler than the corresponding UNION query.) A query on the MERGE table is similar to a UNION that selects corresponding columns from the individual tables that make up the MERGE table. That is, SELECT on a MERGE table is like UNION ALL (duplicates are not removed), and SELECT DISTINCT is like UNION (duplicates are removed).