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:
