Answer includes concept and difference between union and union all sql clause with example. Also, difference between union and join as a note has been included.
Answer:
UNION and UNION ALL:
Used to combine the result set of 2 or more SELECT statements.
- UNION sql statement returns sorted result set with unique (UNION effectively performs SELECT DISTINCT) rows.
- UNION ALL sql statement returns unsorted result set including duplicate rows.
NOTE:
- To use UNION & UNION ALL, number of columns, data types and the order of the columns must be same in each select statement. However, number of equal rows is not required.
- If all the rows are already unique, use UNION ALL instead of UNION, it gives faster results as UNION ALL doesn’t perform SELECT DISTINCT. ORDER BY clause can be used to sort the result.
Union and Union All Example
Below are the C++ and JAVA project tables for Employees. We have to List the employees who are assigned to either C++ or JAVA projects.
C++
NAME | POST |
Scott | Software Engg. |
Amy | Technical lead |
Lisa | Manager |
JAVA
NAME | POST |
Ben | Software Engg. |
Amy | Technical lead |
On performing union query on table C++ and Java following will be the result
SELECT NAME FROM C++
UNION
SELECT NAME FROM JAVA
NAME | POST |
Amy | Technical lead |
Ben | Software Engg |
Lisa | Manager |
Scott | Software Engg |
On performing Union All query on table C++ and Java following will be the result
SELECT NAME FROM C++
UNION ALL
SELECT NAME FROM JAVA
NAME | POST |
Scott | Software Engg |
Amy | Technical lead |
Lisa | Manager |
Ben | Software Engg |
Amy | Technical lead |
To Focus:
ORDER BY with UNION ALL:
ORDER BY should be used only on the last select statement in the UNION ALL queries otherwise it will throw an error.
SELECT NAME FROM C++
UNION ALL
SELECT NAME FROM JAVA
ORDER BY POST
UNION Vs UNION ALL:
- UNION removes duplicate rows, but UNION ALL doesn’t.
- UNION performs sort before removing duplicates. Hence, slower performance than UNION ALL as it doesn’t sort result set?
UNION Vs JOIN
- UNION combines result set of 2 or more SELECT statements whereas JOIN retrieves data from 2 or more tables based on logical relationship (Primary Key & Foreign Key) between tables.
- In other words, UNION combines rows from 2 or more tables, where JOIN combines columns.