MySQL –– UNION
written by: admin
Date Written: 4/15/10
Last Updated: 10/9/13
Here is the syntax for using
UNION. The results will be listed one table on top of the other. In this example the results from
anime are listed first. The results from
papers are then listed and finally the results from
manga are listed. This is different than the listing the results side by side as seen in
JOIN, which is decidedly different.
(SELECT ID, title FROM anime ORDER BY ID ASC LIMIT 10)
UNION
(SELECT ID, title FROM papers ORDER BY ID ASC LIMIT 10)
UNION
(SELECT ID, title FROM manga ORDER BY ID ASC LIMIT 10)
Here are the results that I get:
1 Midori Days: Complete Series
2 Elfen Lied: Vector One
3 Cowboy Bebop: Session 1
4 Puppet Princess
5 Generator Gawl DVD 1: Human Heart Metal Soul
6 Haibane Renmei DVD 1: New Feathers
7 Iria: Zeiram the Animation
8 Panda and the Magic Serpent
9 Battle Angel
10 Noir DVD 3: The Firing Chamber
1 Circa 21
2 pt1 final exam questions
3 About Animeviews
4 Game Links
5 Site version
6 Editor Changelog
7 Dr. Who sites
8 pt1 midterm
9 hulu videos watched
10 movie times
14 Megatokyo Vol.1
15 Megatokyo Vol.2
16 Megatokyo Vol.3
NOTE: You do not need to select ID to sort by ID. If you select the ID the ID will be displayed. The reason there are only three results for the last table is because there are only three rows in that table with IDs 14, 15, and 16.
Sorting
To sort all of the tables place the sorting command after the last SELECT statement and outside of the parentheses.
(SELECT title, ID FROM anime)
UNION
(SELECT title, ID FROM misc)
UNION
(SELECT title, ID FROM manga)
ORDER BY ID ASC LIMIT 10
Rules to Remember
- Each table must have the same number of columns as each other.
- The data types of the corresponding columns must be the same.
To get around the inability to use UNION when you have different numbers of columns you can use "
NULL as col", or "
'' as col". This can be seen in the following example:
(SELECT ID, title, '' AS missing FROM anime_review WHERE (lcase( concat(cast(ID AS char), summary, IFNULL(image,''))) LIKE '%e%' ))
UNION
(SELECT ID, ID, title FROM misc WHERE (lcase( concat(cast(ID AS char), summary)) LIKE '%e%' ))
UNION
(SELECT ID, title, date2 FROM manga_review WHERE (lcase( concat(cast(ID AS char), summary, IFNULL(image,''))) LIKE '%e%' ))
limit 50
"
missing" is just a made up column name.
Here's two more examples of using an alias for a column name
SELECT f1, f2, 'luck' AS f5 FROM tablename
In the above example '
f5' is a made up column name that does not exist in tablename. One possible use for this is to create a virtual column the defines the name of your tables, so you can keep track of which table each row came from.
(SELECT ID, date FROM table1)
UNION
(SELECT ID, date2 AS date FROM table2)
ORDER BY date
In the above example column name
date2 has been temporarily renamed to
date.
Links
UNION
MySQL –– JOINTAGS: mysql