MySQL UNION - Complete Tutorial

 

What is a union?

Unions combine the results from multiple SELECT queries into a consolidated result set.

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

 

 

Suppose we have two tables as follows

MySQL UNION - Complete Tutorial MySQL UNION - Complete Tutorial

 

Let's now create a UNION query to combines both tables using DISTINCT

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

 

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

MySQL UNION - Complete Tutorial

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

Let's now create a UNION query to combines both tables using ALL

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

Here duplicate rows are included and since we use ALL.

MySQL UNION - Complete Tutorial

Why use unions

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.

Summary

  • The UNION command is used to combine more than one SELECT query results into a single query contain rows from all the select queries.
  • The number of columns and data types in the SELECT statements must be the same in order for the UNION command to work. 
  • The DISTINCT clause is used to eliminate duplicate values from the UNION query result set. MySQL uses the DISTINCT clause as the default when executing UNION queries if nothing is specified.
  • The ALL clause is used to return all even the duplicate rows in the UNION query.

Practical examples using MySQL workbench

In our myFlixDB lets combine

membership_number and full_names from Members table

with

movie_id and title from movies table

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.

membership_numberfull_names
1Janet Jones
2Janet Smith Jones
3Robert Phil
4Gloria Williams
5Leonard Hofstadter
6Sheldon Cooper
7Rajesh Koothrappali
8Leslie Winkle
9Howard Wolowitz
1667% Guilty
6Angels and Demons
4Code Name Black
5Daddy's Little Girls
7Davinci Code
2Forgetting Sarah Marshal
9Honey mooners
19movie 3
1Pirates of the Caribean 4
18sample movie
17The Great Dictator
3X-Men