- 2.1. The Server SQL Mode
- 2.2. MySQL Identifier Syntax and Naming Rules
- 2.3. Case Sensitivity in SQL Statements
- 2.4. Character Set Support
- 2.5. Selecting, Creating, Dropping, and Altering Databases
- 2.6. Creating, Dropping, Indexing, and Altering Tables
- 2.7. Obtaining Database Metadata
- 2.8. Performing Multiple-Table Retrievals with Joins
- 2.9. Performing Multiple-Table Retrievals with Subqueries
- 2.10. Performing Multiple-Table Retrievals with UNION
- 2.11. Multiple-Table Deletes and Updates
- 2.12. Performing Transactions
- 2.13. Foreign Keys and Referential Integrity
- 2.14. Using FULLTEXT Searches
This chapter is from the book
This chapter is from the book
This chapter is from the book
2.10. Performing Multiple-Table Retrievals with UNION
To create a result set that combines the results from several queries, use 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; +------+------+ | j | c | +------+------+ | -1 | tan | | 1 | red | +------+------+ mysql> SELECT * FROM t3; +------------+------+ | d | k | +------------+------+ | 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, write multiple SELECT statements and put the keyword UNION between them. Each SELECT must retrieve the same number of columns. For example, to select the integer column from each table, do this:
mysql> SELECT i FROM t1 UNION SELECT j FROM t2 UNION SELECT k FROM t3; +------+ | i | +------+ | 1 | | 2 | | 3 | | -1 | | 100 | | 200 | +------+
UNION has the following properties.
Column name and data types. The column names for 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 a UNION such that corresponding columns do have the same types, but MySQL performs type conversion as necessary if they do not.) Column matching occurs 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 k, 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, k 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.
Duplicate-row handling. 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.
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.
ORDER BY and LIMIT handling. To sort a UNION result as a whole, place each SELECT within parentheses and add an ORDER BY clause following the last one. 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 k, d FROM t3)
-> ORDER BY c;
+------+------------+
| i | c |
+------+------------+
| 100 | 1904-01-01 |
| 200 | 2004-01-01 |
| 2 | blue |
| 3 | green |
| 1 | red |
+------+------------+
If a sort column is aliased, an ORDER BY at the end of the UNION must refer to the alias. Also, the ORDER BY cannot refer to table names. If you need to sort by a column specified as tbl_name.col_name in the first SELECT, alias the column and refer to the alias in the ORDER BY clause.
Similarly, to limit the number of rows returned by a UNION, add LIMIT to the end of the statement:
mysql> (SELECT * FROM t1) UNION (SELECT * FROM t2) UNION (SELECT * FROM t3)
-> LIMIT 2;
+------+------+
| i | c |
+------+------+
| 1 | red |
| 2 | blue |
+------+------+
ORDER BY and LIMIT also can be used within a parenthesized individual SELECT to apply only to that SELECT:
mysql> (SELECT * FROM t1 ORDER BY i LIMIT 2)
-> UNION (SELECT * FROM t2 ORDER BY j LIMIT 1)
-> UNION (SELECT * FROM t3 ORDER BY d LIMIT 2);
+------------+------+
| i | c |
+------------+------+
| 1 | red |
| 2 | blue |
| -1 | tan |
| 1904-01-01 | 100 |
| 2004-01-01 | 200 |
+------------+------+
ORDER BY within an individual SELECT is used only if LIMIT is also present, to determine which rows the LIMIT applies to. It does not affect the order in which rows appear in the final UNION result.