Explain UNION and UNION ALL SQL Clause with example

(Last Updated On: March 17, 2017)

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

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

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.

 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.