MySQL | UNION
Start your free 7-days trial now!
MySQL's UNION clause combines the results from multiple SELECT statements into a single result set.
In order to perform a UNION on tables, they must first satisfy the following conditions:
• Same number of columns
• Corresponding columns must have compatible data types
Syntax
SELECT column(s) FROM table1UNIONSELECT column(s) FROM table2;
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Also consider the following table about some professors:
professor_id | fname | lname | day_hired | age | username |
|---|---|---|---|---|---|
1 | Tom | Willox | 2012-11-03 | 47 | twillox1 |
2 | Ralph | Skeen | 2009-07-30 | 39 | rskeen2 |
3 | Hyun | Lee | 2014-02-21 | 32 | hlee3 |
4 | Bryce | Miller | 2004-10-01 | 53 | bmiller4 |
The above sample table can be created using the code here.
Basic usage
To return a list of the names of all students and professors at the school:
SELECT fname, lname FROM studentsUNIONSELECT fname, lname FROM professors;
+----------+--------+| fname | lname |+----------+--------+| Sky | Towner || Ben | Davis || Travis | Apple || Arthur | David || Benjamin | Town || Tom | Willox || Ralph | Skeen || Hyun | Lee || Bryce | Miller |+----------+--------+
We can see that the results from the individual SELECT statements have been combined into one result set. In this way we are able to see the records from students and professors tables together.
By default, duplicates are removed from the union set.( UNION DISTINCT is the same as just UNION). UNION ALL will allow for duplicates.
Different number of columns
We can only perform UNION when the SELECT statements involved return the same number of columns:
SELECT fname, lname, username FROM studentsUNIONSELECT fname, lname FROM professors;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
Here we get an error as the first SELECT statement returns three columns fname, lname, username while the second SELECT statement only returns two fname and lname.
