UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:
SELECT query UNION ALL SELECT query ...
You can combine as many SELECT queries as you need. A SELECT query can include any valid SELECT format, including WHERE clauses, LIMIT clauses, and so on. The rules for the queries are
- All the select queries must select the same number of columns.
- The columns selected in the queries must contain the same type of data.
The result set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the result set are the column names from the first SELECT query. The series of SELECT queries can select different columns from the same table, but situations in which you want a new table with one column in a table followed by another column from the same table are unusual. It’s much more likely that you want to combine columns from different tables. For example, you might have a table of members who have resigned from the club and a separate table of current members. You can get a list of all members, both current and resigned, with the following query:
SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember
The result of this query is the last and first names of all current members, followed by the last and first names of all the members who have resigned. Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.
You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire result set, rather than just to the query that it follows, use parentheses as follows:
(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName
The UNION statement was introduced in MySQL 4.0. It is not available in MySQL 3.
SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember
The result of this query is the last and first names of all current members, followed by the last and first names of all the members who have resigned. Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.
You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire result set, rather than just to the query that it follows, use parentheses as follows:
(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName
The UNION statement was introduced in MySQL 4.0. It is not available in MySQL 3.
No comments:
Post a Comment