Post
Ask Question

Working with Union in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about working with MySQL UNION operator with examples. Without wasting time, let’s start.
 

UNION IN MySQL

 
UNION is used for combining two or more separate commands to return a single output. The UNION operator is used to combine the result-set of two or more SELECT statements. UNION is useful because we have some information in multiple tables and we want to fetch rows from all of them at once. We want to select several sets of rows from the same table, but some conditions that characterize each set are not easy to write as a single WHERE clause. UNION allows retrieval of each set with a simpler WHERE clause in its own SELECT statement. The rows retrieved by each are combined and produced as the final command result.
 
The unions are useful if you wish to join the results from more than one query, but be aware that the number of the returned columns by each select query should be the same.
 
Syntax :
SELECT column1, column2, …
UNION [ALL | DISTINCT]
SELECT column1, column2, …
[UNION [ALL | DISTINCT]
SELECT column1, column2, …
 
Example : 
  1. SELECT id, name FROM my_table 

UNION 

 
SELECT id, name FROM my_table;
 
Without wasting time, let’s see the example…
  1. CREATE DATABASE UNION_OPERATORS;  
  2. USE UNION_OPERATORS;  
  3. /* Create a table */  
  4. CREATE TABLE U1(  
  5. U_id int NOT NULL  
  6. );  
  7.    
  8. CREATE table U2(  
  9. U_id int NOT NULL  
  10. );  
  11.    
  12. /* Insert some values into them... */  
  13. Insert into U1 values (101), (201), (301);  
  14. Insert into U2 values (51), (201), (301);  
  15.    
  16. /* Now, retrieve the result from SELECT query */  
  17. SELECT U_id  
  18. FROM U1  
  19. UNION  
  20. SELECT U_id  
  21. FROM U2; 
 
Now, try to understand the concept of UNION with a Venn diagram.
 
 

UNION ALL

 
The UNION ALL operator selects all rows from each table and combines it into a single table. The UNION and UNION ALL are different in that UNION ALL will not drop duplicate rows and UNION eliminates duplicate rows. It just pulls all rows from all tables fitting our command specifics and combines them into a table.
  1. SELECT U_id  
  2. FROM U1  
  3. UNION ALL  
  4. SELECT U_id  
  5. FROM U2; 
 

Union Joins

 
Union Join allows the results of two commands to be combined into one output result. Union Join is done by having 2 or more commands together by the UNION operator.
 
Syntax
SELECT <fields>
FROM <table_name>
WHERE <conditions>
UNION
SELECT <fields>
FROM <table_name>
WHERE <conditions>
 
Example: This example shows the combination of two queries with one result set.
  1. SELECT * FROM emp_info    
  2. WHERE emp_status = 'CONFIRMED'    
  3. UNION    
  4. SELECT * FROM emp_info    
  5. WHERE emp_status = 'Notice'  
 

UNION vs JOIN

 
The basic difference between the UNION and JOIN is that UNION combines the result set horizontally whereas the JOIN statement combines the result sets vertically. The below picture will illustrate the difference between the UNION statement and JOIN.
 
 
Now, take some examples from a dummy database which I have attached with this article, in which we have some tables and data inside it.
  1. SELECT * FROM bookorder; 
  1. SELECT * FROM library; 
  1. SELECT booknumber  
  2. FROM bookorder  
  3. UNION  
  4. SELECT booknumber  
  5. FROM library; 
 

MySQL UNION and ORDER BY

 
We can use the ORDER BY clause with the UNION operator.
  1. SELECT booknumber  
  2. FROM bookorder  
  3. UNION  
  4. SELECT booknumber  
  5. FROM library  
  6. ORDER BY booknumber desc; 
 
Reference
https://www.mysqltutorial.org/
 

CONCLUSION

 
In this article, I have discussed the concept of UNION in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 
 
Thanks for reading this article!

Similar Articles