Explain UNION and UNION ALL SQL Clause with example

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++

JAVA

On performing union query on table C++ and Java following will be the result

SELECT NAME FROM C++
UNION
SELECT NAME FROM JAVA

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

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.