Using the ‘UNION’ Operator in MySQL and MariaDB

mysql union

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.

SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *