(SQL examples for Beginners)
In this end-to-end example, you will learn – SQL Tutorials for Business Analyst: How to use Unions in MySQL.
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
Let’s now create a UNION query to combines 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.
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.
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_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 |
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
Python Example – Write a Python program to create a union of sets.
PostgreSQL tutorial for Beginners – PostgreSQL – UNIONS Clause