MySQL SELECT queries are commands that pull data from your database tables according to conditions each record must meet.
Complex queries will often need to combine the results from two or more SELECT queries – this is what the UNION operator does.
UNION Syntax
SELECT column [, column2, column3...] FROM first_table UNION SELECT column [, column2, column3...] FROM second_table;
Note that:
- Each SELECT statement must have the same number of columns in its results
- Data type in each of the columns must match
- The columns in the results from each statement must be in the same order
- SELECT statements can include WHERE, LIMIT, and other clauses and constraints
- The column names in the results will usually be the same as the column names in the first SELECT statement
- The results from a UNION will omit duplicate rows
Union ALL
The UNION ALL operator works the same way as the UNION operator, but it will include duplicate rows:
SELECT column [, column2, column3...] FROM first_table UNION ALL SELECT column [, column2, column3...] FROM second_table;
Example
cars table:
car_id | car_name | color |
---|---|---|
1 | Ford Focus | Blue |
2 | Holden Commodore | Red |
3 | Datsun Sunny | Orange |
4 | Trabant 601 | Green |
fruits table:
fruit_id | fruit_name | color |
---|---|---|
1 | Apple | Red |
2 | BlueBerry | Blue |
3 | Lime | Green |
4 | Orange | Orange |
SELECT car_name AS name, colour FROM cars UNION SELECT fruit_name, colour FROM fruits;
UNION Results
name | color |
---|---|
Ford Focus | Blue |
Holden Commodore | Red |
Datsun Sunny | Orange |
Trabant 601 | Green |
Apple | Red |
BlueBerry | Blue |
Lime | Green |
Orange | Orange |
The UNION operator has merged the results from both tables, using the column names defined in the first SELECT query.
Conclusion
The MySQL UNION operator makes combining the results from SELECT queries quick and painless and is a good tool for getting a quick combined result set from several tables with similar columns.
For more MySQL and MariaDB tips and tricks, check out our other database articles.