Using the ‘UNION’ Operator in MySQL and MariaDB

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:

fruits table:

SELECT car_name AS name, colour
FROM cars
UNION
SELECT fruit_name, colour
FROM fruits;

UNION Results

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.