Sql UNION and UNION ALL

UNION keyword in SQL is used to combine SELECT statement results without any duplicate. In order to use UNION and combine results both SELECT statements should have a same number of columns with the same data type in the same order, but the length of the column can be different.
Section 24.1: Basic UNION ALL query
CREATE TABLE HR_EMPLOYEES
(
PersonID int,
LastName VARCHAR(30),
FirstName VARCHAR(30),
Position VARCHAR(30)
);
CREATE TABLE FINANCE_EMPLOYEES
(
PersonID INT,
LastName VARCHAR(30),
FirstName VARCHAR(30),
Position VARCHAR(30)
);
Let's say we want to extract the names of all the managers from our departments.
Using a UNION we can get all the employees from both HR and Finance departments, which hold the position of a manager
SELECT
FirstName, LastName
FROM
HR_EMPLOYEES
WHERE
Position = 'manager'
UNION ALL
SELECT
FirstName, LastName
FROM
FINANCE_EMPLOYEES
WHERE
Position = 'manager'
The UNION statement removes duplicate rows from the query results. Since it is possible to have people having the same Name and position in both departments we are using UNION ALL, in order not to remove duplicates.
If you want to use an alias for each output column, you can just put them in the first select statement, as follows:
SELECT
FirstName as 'First Name', LastName as 'Last Name'
FROM
HR_EMPLOYEES
WHERE
Position = 'manager'
UNION ALL
SELECT
FirstName, LastName
FROM
FINANCE_EMPLOYEES
WHERE
Position = 'manager'
Simple explanation and Example
In simple terms:
- UNION joins 2 result sets while removing duplicates from the result set
- UNION ALL joins 2 result sets without attempting to remove duplicates
One mistake many people make is to use a UNION when they do not need to have the duplicates removed. The additional performance cost against large results sets can be very significant.
When you might need UNION
Suppose you need to filter a table against 2 different attributes, and you have created separate non-clustered indexes for each column. A UNION enables you to leverage both indexes while still preventing duplicates.
SELECT C1, C2, C3 FROM Table1 WHERE C1 = @Param1
UNION
SELECT C1, C2, C3 FROM Table1 WHERE C2 = @Param2
This simplifies your performance tuning since only simple indexes are needed to perform these queries optimally. You may even be able to get by with quite a bit fewer non-clustered indexes improving overall write performance against the source table as well.
When you might need UNION ALL
Suppose you still need to filter a table against 2 attributes, but you do not need to filter duplicate records (either because it doesn't matter or your data wouldn't produce any duplicates during the union due to your data model design).
SELECT C1 FROM Table1
UNION ALL
SELECT C1 FROM Table2
This is especially useful when creating Views that join data that is designed to be physically partitioned across multiple tables (maybe for performance reasons, but still wants to roll up records). Since the data is already split, having the database engine remove duplicates adds no value and just adds additional processing time to the queries.
ATutorialHub Related Guide
Comments (9)
User Comments

panduranga gupta
2021-07-05 07:03:13good website for learning and help me a lot

raju
2021-09-25 14:58:47The awsome website i am looking like for a long time, good work atutorialhub team keep doing

Shivani
2021-09-01 15:03:56Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

Harshitha
2021-09-10 15:05:45It is very helpful to students and easy to learn the concepts

Sowmya
2021-09-14 15:06:41Great job Tutorials are easy to understand Please make use of it

Zain Khan
2021-09-18 15:07:23Great content and customized courses.

Rudrakshi Bhatt
2021-09-09 15:08:10Well structured coursed and explained really well!

Pavana Somashekar
2021-09-11 15:09:08Good platform for beginners and learn a lot on this website

Sax
2021-09-25 19:35:50Nice website
Leave a Comment