Added to MySQL in version 4.0 is the UNION. UNIONs are like JOINs in that they work on multiple tables (or the same table multiple times). With a JOIN, the returned results are normally based upon a condition, where a column in one table relates to a column in another. With a UNION, no such relationship is necessary, but the selected data from both tables should be of the same type. Specifically, the nth column selected in the first query should be of the same type as the nth column selected in the second query.
The basic syntax of a UNION is
SELECTquery1 UNION SELECTquery2
Assuming you have the two dummy tables shown in Figure 10.22, you can see the result of the following UNION in Figure 10.23.
Figure 10.22. I'll use these two dummy tables to quickly demonstrate how UNIONs behave.
Figure 10.23. The contents of two unrelated columns from two unrelated tables are merged into one result set.
SELECT a_string FROM table1 UNION SELECT this_string FROM table2
You should notice a couple of things. First, the column names from the first table are used in the results. Second, any duplicate records will be removed (cat only appears once even though it's in both tables). This is true unless you add the ALL keyword (Figure 10.24):
Figure 10.24. With a UNION ALL query, duplicates are not removed (compare with Figure 10.23).
SELECT a_string FROM table1 UNION ALL SELECT this_string FROM table2;
I'll play with some examples in the following steps, but also see the sidebar for a discussion as to when UNIONs are most useful.
To use UNIONs: