- MySQL Naming Rules
- The Server SQL Mode
- Character Set Support
- Selecting, Creating, Dropping, and Altering Databases
- Creating, Dropping, Indexing, and Altering Tables
- Getting Information About Databases and Tables
- Performing Multiple-Table Retrievals with Joins
- Performing Multiple-Table Retrievals with Subqueries
- Performing Multiple-Table Retrievals with UNION
- Multiple-Table Deletes and Updates
- Performing Transactions
- Foreign Keys and Referential Integrity
- Using FULLTEXT Searches
- New Features in MySQL 5.0
This chapter is from the book
This chapter is from the book
This chapter is from the book
Performing Multiple-Table Retrievals with UNION
If you want to create a result set that combines the results from several queries, you can do so by using a UNION statement. For the examples in this section, assume that you have three tables, t1, t2, and t3 that look like this:
mysql> SELECT * FROM t1; +------+-------+ | i | c | +------+-------+ | 1 | red | | 2 | blue | | 3 | green | +------+-------+ mysql> SELECT * FROM t2; +------+------+ | i | c | +------+------+ | -1 | tan | | 1 | red | +------+------+ mysql> SELECT * FROM t3; +------------+------+ | d | i | +------------+------+ | 1904-01-01 | 100 | | 2004-01-01 | 200 | | 2004-01-01 | 200 | +------------+------+
Tables t1 and t2 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 i FROM t1 UNION SELECT i FROM t2 UNION SELECT i FROM t3; +------+ | i | +------+ | 1 | | 2 | | 3 | | -1 | | 100 | | 200 | +------+
UNION has the following properties:
-
The names for the columns of the UNION result come from the names of the columns in the first SELECT. The second and subsequent SELECT statements in the UNION must select the same number of columns, but corresponding columns need not have the same names or data types. (Normally, you write UNION such that corresponding columns do have the same types, but MySQL performs type conversion as necessary if they do not.) Columns are matched by position rather than by name, which is why the following two statements return different results, even though they select the same values from the two tables:
mysql> SELECT i, c FROM t1 UNION SELECT i, d FROM t3; +------+------------+ | i | c | +------+------------+ | 1 | red | | 2 | blue | | 3 | green | | 100 | 1904-01-01 | | 200 | 2004-01-01 | +------+------------+ mysql> SELECT i, c FROM t1 UNION SELECT d, i FROM t3; +------------+-------+ | i | c | +------------+-------+ | 1 | red | | 2 | blue | | 3 | green | | 1904-01-01 | 100 | | 2004-01-01 | 200 | +------------+-------+
In each statement, the data type for each column of the result is determined from the selected values. In the first statement, strings and dates are selected for the second column. The result is a string column. In the second statement, integers and dates are selected for the first column, strings and integers for the second column. In both cases, the result is a string column.
Note: Before MySQL 4.1.1, the data type for each column of the UNION result is based only on the type of the column in the first SELECT, and values from corresponding column of the following SELECT statements are converted to that type. Each of the preceding two statements will return an integer first column, which results in type conversion of the dates to integers.
By default, UNION eliminates duplicate rows from the result set:
mysql> SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; +------------+-------+ | i | c | +------------+-------+ | 1 | red | | 2 | blue | | 3 | green | | -1 | tan | | 1904-01-01 | 100 | | 2004-01-01 | 200 | +------------+-------+
t1 and t2 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.
UNION DISTINCT is synonymous with UNION; both retain only distinct rows.
If you want to preserve duplicates, change each UNION to UNION ALL:
mysql-> SELECT * FROM t1 UNION ALL SELECT * FROM t2
UNION ALL SELECT * FROM t3;
+------------+-------+
| i | c |
+------------+-------+
| 1 | red |
| 2 | blue |
| 3 | green |
| -1 | tan |
| 1 | red |
| 1904-01-01 | 100 |
| 2004-01-01 | 200 |
| 2004-01-01 | 200 |
+------------+-------+
If you mix UNION or UNION DISTINCT with UNION ALL, any distinct union operation takes precedence over any UNION ALL operations to its left.
To sort a UNION result as a whole, place each SELECT within parentheses and add an ORDER BY clause following the final closing parenthesis. 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:
mysql> (SELECT i, c FROM t1) UNION (SELECT i, d FROM t3)
-> ORDER BY c;
+------+------------+
| i | c |
+------+------------+
| 100 | 1904-01-01 |
| 200 | 2004-01-01 |
| 2 | blue |
| 3 | green |
| 1 | red |
+------+------------+
To sort the result from an individual SELECT statement within a UNION, place the ORDER BY within the appropriate parenthesized SELECT:
mysql> (SELECT i, c FROM t1 ORDER BY i DESC)
-> UNION (SELECT i, c FROM t2 ORDER BY i);
+------+-------+
| i | c |
+------+-------+
| 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 t1) UNION (SELECT * FROM t2) UNION (SELECT * FROM t3)
-> LIMIT 1;
+------+------+
| i | c |
+------+------+
| 1 | red |
+------+------+
If enclosed within parentheses as part of an individual SELECT statement, it applies only to that SELECT:
mysql> (SELECT * FROM t1 LIMIT 1)
-> UNION (SELECT * FROM t2 LIMIT 1)
-> UNION (SELECT * FROM t3 LIMIT 1);
+------------+------+
| i | c |
+------------+------+
| 1 | red |
| -1 | tan |
| 1904-01-01 | 100 |
+------------+------+
In a UNION statement, you need not select from different tables. You can select results from different subsets of the same table, using different conditions for each SELECT. 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.
You can "simulate" UNION by selecting rows from each table into a temporary table and then selecting the contents of that table. If you make the table a TEMPORARY table, it will be dropped automatically when your session with the server terminates. For quicker performance, use a MEMORY table:
CREATE TEMPORARY TABLE tmp ENGINE = MEMORY SELECT ... FROM t1 WHERE ... ; INSERT INTO tmp SELECT ... FROM t2 WHERE ... ; INSERT INTO tmp SELECT ... FROM t3 WHERE ... ; ... SELECT * FROM tmp ORDER BY ... ;
If you want to run a UNION-type query on MyISAM tables that have the same structure, you can set up a MERGE table and query that. One reason this is useful is that it is simpler to write a query on a MERGE table than the corresponding UNION statement. 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 or UNION DISTINCT (duplicates are removed).