Basics of the Oracle 12c Data Pump
How to Retrieve Data from Specific Rows in MySQL Databases
How MySQL Works

Combine Information from Multiple MySQL Tables with UNION

You can combine information to from more than one MySQL table by using the UNION query. With UNION, rows are retrieved from one or more tables and stored together, one after the other, in a single result. For example, if your query selected 6 rows from one table and 5 rows from another table, the result would contain 11 rows.

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 contains 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 (OldMember) and a separate table of current members (Member). 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 aren’t added to the result.

You can use ORDER BY with each SELECT query, 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
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Use Flashback Query in Oracle 12c
Basics of Oracle 12c's Flashback Database
Basics of Physical and Logical Standby Oracle 12c Databases
Basics of Object Privileges and Management in Oracle 12c
How to Create and Manage Enterprise Manager Database Express Users in Oracle 12c
Advertisement

Inside Dummies.com