Previous Code Entry
SQL Server Core Installation Script
Current Code Entry
Go Back To Code List
[SQL Server]
Difference between UNION ALL and UNION
Article Entry Date: December 28,2022 @ 04:49:07 / Last Updated On: December 28,2022 @ 04:49:07
The difference between using UNION ALL and UNION to display records correctly.
I recently needed data from multiple tables with the same column name to be displayed in the same ROW.
There are two ways to perform this action.
[UNION ALL] and [UNION].
I found out that if you use [UNION ALL], it will list all the rows but duplicate the top Select statement.
Example.
[_00TabOne] had the following data.
ID    Ordered
1    3
1    5
1    8
1    10

[_00TabTwo] had the following data.
ID    Ordered
1    1
1    2
1    4
1    6
1    7
1    9

The above two tables have the following columns in common. [Ordered] and [ID].
So, in our SQL Query, we would do something like this.

Our output will be this.
[Ordered]
1
2
3
3<
4
5
5<
6
7
8
8<
9
10
10<
As you can see, the [_00TabTwo] is duplicated (Highlighted with an arrow), whereas the [_00TabOne] is a single record as it is supposed to be.
So, now let's look at UNION].


[Ordered]
1
2
3
4
5
6
7
8
9
10

Now, the above is what I needed for my project. I needed this order to display the records from the other tables correctly.