MySQL UNION – Complete Tutorial

What is a UNION in MySQL?

UNION is a MySQL operator that combines the results from multiple SELECT queries into a consolidated result set. The rows returned by the second query are placed underneath the rows returned by the first, which produces one vertical list instead of two separate ones.

The only requirement for this to work is that the number of columns should be the same from all the SELECT queries which need to be combined.

Suppose we have two tables as follows.

Both tables hold two columns of the same kind, so they are eligible for a union. The examples that follow combine exactly these two tables.

Why Use UNION?

Suppose there is a flaw in your database design and you are using two different tables meant for the same purpose. You want to consolidate these two tables into one while omitting any duplicate records from creeping into the new table. You can use UNION in such cases.

The operator is also useful in everyday reporting work:

• Archived and live data: A current table and an archive table that share the same columns can be reported on together, without physically merging them.
• Several sources, one report: Members and movies, or sales from two regions, can be listed in a single output for a quick audit.
• Migration checks: Rows from the old table and the new table can be stacked and compared before the old table is dropped.

A union is not a substitute for a JOIN. UNION adds rows underneath rows, whereas a JOIN adds columns beside columns, and that distinction decides which operator the task calls for.

MySQL UNION Syntax and Rules

Now that the purpose is clear, look at the shape of the statement and the rules the database enforces.

SELECT column1, column2 FROM `table1`
UNION [DISTINCT | ALL]
SELECT column1, column2 FROM `table2`;

Three rules govern every union:

1. Equal column count. Each SELECT statement must return the same number of columns, otherwise MySQL raises error 1222.
2. Compatible data types in the same order. Column one of the first query is matched with column one of the second, so a number should meet a number and text should meet text.
3. Names come from the first query. The heading of the result set is taken from the first SELECT, which is why any alias belongs there.

An ORDER BY or a LIMIT clause placed at the end applies to the combined result rather than to one branch of it, and it must reference the column names produced by the first SELECT.

UNION DISTINCT vs UNION ALL

With the rules in place, the remaining decision is whether duplicate rows should survive.

Combining Tables Using DISTINCT

Let us now create a UNION query to combine both tables using DISTINCT.

SELECT column1, column2 FROM `table1`
UNION DISTINCT
SELECT column1, column2 FROM `table2`;

Here duplicate rows are removed and only unique rows are returned.

Note: MySQL uses the DISTINCT clause as default when executing UNION queries if nothing is specified.

Combining Tables Using ALL

Let us now create a UNION query to combine both tables using ALL.

SELECT `column1`, `column2` FROM `table1`
UNION ALL
SELECT `column1`, `column2` FROM `table2`;

Here duplicate rows are included, since we use ALL.

The two images make the difference easy to see, and the table below summarises it.

💡 Tip: If the two branches cannot produce duplicate rows, choose UNION ALL. The database then skips the sort and comparison work that DISTINCT requires, which is a noticeable saving on large tables.

Practical Example Using MySQL Workbench

The examples so far used sample tables. The same query now runs against the real myflixdb database, where the two tables hold quite different records.

In our myFlixDB, let us combine the membership_number and full_names columns from the members table with the movie_id and title columns from the movies table. Both queries return two columns, so the union is valid.

We can use the following query.

SELECT `membership_number`, `full_names` FROM `members`
UNION
SELECT `movie_id`, `title` FROM `movies`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below. Notice that the headings come from the first SELECT, even though the lower rows are movie records.

membership_number
full_names



1
Janet Jones


2
Janet Smith Jones


3
Robert Phil


4
Gloria Williams


5
Leonard Hofstadter


6
Sheldon Cooper


7
Rajesh Koothrappali


8
Leslie Winkle


9
Howard Wolowitz


16
67% Guilty


6
Angels and Demons


4
Code Name Black


5
Daddy's Little Girls


7
Davinci Code


2
Forgetting Sarah Marshal


9
Honey mooners


19
movie 3


1
Pirates of the Caribean 4


18
sample movie


17
The Great Dictator


3
X-Men

FAQs