Joining Tables - Database Design Manual: Using MySQL for Windows - page 84

Figure 7.14 Two different tables joined with UNION.

The above can be re-written as it is selecting data from the same table. The beauty of the
UNION command is that it can join data from different tables, as long as the columns you
are choosing have similar datatypes. For example, both the cookies table and the log table
have ID fields and date fields. How would we combine the ID and the dates from both tables
into one results set? You could attempt it using a join as follows:

SELECT log.ID, log.datecreated, cookies.cookieid, cookies.datecreated

FROM log,cookies

However, the above will produce a large result set, as it is an unrestricted join, every row
in the log table returned with a row from the cookies table producing 30 rows as a result. We
just want the results with the rows from both of the two tables. We will try this query using
a union:

SELECT ID, datecreated
FROM log
UNION
SELECT cookieid, datecreated

FROM cookies

This produces a UNION of the two different tables on the ID and datecreated columns.
The results are shown in Figure 7.14.
If you have been working through all of the examples in this topic, your cookie table
should contain 6 rows and your log table 5 rows. However, the result set, shown in the fig-
ure, only contains 10 rows. This is because the datecreated and the ID fields for both tables
are the same for ID = 1. MySQL has therefore treated this as duplicate entry and removed
one of the duplicates in the result set. As we have never specified the time when creating
these fields, MySQL always defaults to 00:00:00. If our examples had used more accurate
date/time fields, the difference in the time columns would have stopped this being treated
as a duplicate row.