SQL Server – Union and Union All, it’s not the same stuff

Hey Geeks,

You might have the knowledge of it, but do you know that there’s a difference between this two of them. So I will try to give brief on this:

Union:

Union is a command which is being used to combine the results of two or more query. It can also be defined as the combination of selected rows from same or different table into a result set. The advantage of using Union is that they only select unique or distinct rows into the result set. Union can also be somewhat related to Join where there is a combination of different columns, but they return the results only for the two tables. While here you can query your results for more than two tables.

Here the most important thing is that all the selected columns need to have the similar data type and the attribute name should also be same, because duplicity is not allowed.

The syntax common for both the Union and Union All:

{ <query_specification> | ( <query_expression> ) } 
  UNION [ ALL ] 
  <query_specification | ( <query_expression> ) 
 [ UNION [ ALL ] <query_specification> | ( <query_expression> ) 
    [ ...n ] ]

This will more clearly be understood by going through an example:

CREATE DATABASE TEST
GO
 
USE TEST
GO
 
CREATE TABLE Students1(
SID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
)

Here you can also see that it’s a Union are being used within the Single table itself:

INSERT INTO Students1
SELECT 1, 'Piyush', 'Bilaspur'
UNION
SELECT 2, 'Amit', 'Kolkata'
UNION
SELECT 3, 'Arjit', 'Ambikapur'
CREATE TABLE Students2(
SID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50),
Cell_No NUMERIC
)
INSERT INTO Students2
SELECT 1, 'Piyush', 'Bilaspur',9900108790
UNION
SELECT 2, 'Robin', 'Chennai',9089786789
UNION
SELECT 3, 'Chetan', 'Mumbai',9009087675

So now if I want a Union of these two tables Students1 and Students2, the query will be like:

SELECT SID,Name,City FROM Students1
UNION
SELECT SID,Name,City FROM Students2

Then the result would be like:

You can see that the first row is being occuring in both the tables, but in Union the duplicacy is removed.

If you want to see the results in a separate table, in this case we have named Std, then the query will be:

SELECT SID,Name,City INTO Std FROM Students1
UNION
SELECT SID,Name,City FROM Students2

Union All:

Union All is also a command which is being used to combine the results of two or more query. It can also be defined as the combination of selected rows from same or different table into a result set. Union All selects all the rows into the result set, i.e. there is no distinct behavior. This can be taken as an advantage or sometimes might be as a disadvantage. Union All also have advantage over Union is that they give faster results. So it’s better to use in the case where being known that all the rows are unique.

The Union All also have better Optimal Performance if were compared with the Union.

The Syntax is being common between them, just use Union All in place of Union.

This will more clearly be understood by going through the same example and its almost same just use the Union All in place of Union.

You can create the two tables using Union All also.

I will showcase directly to the step where there comes a difference between the Union and Union All.

If you want a Union All of these two tables Students1 and Students2, the query will be like:

USE TEST
 
SELECT SID,Name,City FROM Students1
UNION ALL
SELECT SID,Name,City FROM Students2

Then the result would be like:

As you can see the two rows are being repeated, i.e. duplicity is allowed here.

Well this was all about Union and Union All in SQL Server.

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook