What is the difference between UNION and UNION ALL?

SQL

What is the difference between UNION and UNION ALL?

1
Answers

Replies

As, we all know that UNION and UNION ALL are operators in SQL. There are several differences between UNION and UNION ALL are given below:


 


 



For Example: In this example, we will implement UNION and UNION ALL operators with the help of database tables are given below:




Case 1: what happens when we perform UNION operation on SALES and PURCHASE tables.



SQL Query: SELECT * FROM SALES


UNION


SELECT * FROM PURCHASES;



In the above query we are performing UNION operation on SALES and PURCHASES so UNION will concatenates all the records from SALES and PURCHASES tables that means we have total 7 records  [ i.e. 4 records in SALES and 3 records in PURCHASES] But, it will return 6 records because if we concatenates records from SALES and PURCHASES then a record comes twice in the result set whose Product_Name is DELL so the repeated record(second time) will be removed from the result set because it does not allow duplicates.



Result:


    


 



Case 2: what happens when we perform UNION ALL operation on SALES and PURCHASE tables.



SQL Query: SELECT * FROM SALES


UNION ALL


SELECT * FROM PURCHASES;



In the above query we are performing UNION ALL operation on SALES and PURCHASES so UNION ALL will concatenates all the records from SALES and PURCHASES tables that means we have total 7 records  [ i.e. 4 records in SALES and 3 records in PURCHASES] But, it will return all the 7 records because if we concatenates records from SALES and PURCHASES then a record comes twice in the result set whose Product_Name is DELL so the repeated record(second time) will not be removed from the result set because it allows duplicates.


Result:


 

 
 

If you want to unleash your potential in this competitive field, please visit the SQL course page for more information, where you can find the SQL tutorials and SQL frequently asked interview questions and answers as well.

 

This topic has been locked/unapproved. No replies allowed

Login to participate in this discussion.

Leave a reply

Before proceeding, please check your email for a verification link. If you did not receive the email, click here to request another.
To Top