Loading

what is the difference between UNION and UNION ALL ?

what is the difference between UNION and UNION ALL ?

Sonu Sharma

Sonu Sharma

8y1.4kSQL

what is the difference between UNION and UNION ALL?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted answer
Hello Sonu,

Union combine the results of different result set and give the unique result set output only as shown below-
SELECT 'RAM' AS NAME

UNION

SELECT 'SHYAM' AS NAME

UNION

SELECT 'SONU' AS NAME

UNION

SELECT 'RAM' AS NAME
The output is as below. Here RAM was twice but in result it has come only once i.e., for unique result.

NAME

RAM

SHYAM

SONU

UNION ALL gives the result set output after combining different result set but it gives all the result with repeated one if the output remains more than one times as below
SELECT 'RAM' AS NAME

UNION ALL

SELECT 'SHYAM' AS NAME

UNION ALL

SELECT 'SONU' AS NAME

UNION ALL

SELECT 'RAM' AS NAME

Output
RAM

SHYAM

SONU

RAM

Here RAM has come twice as it was two times.
+2
Jayesh Vaghasiya8y ago
UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
0
Ranjit Powar8y ago
Check this

https://www.c-sharpcorner.com/UploadFile/3194c4/set-operators-in-sql-server/
0
Code Alone8y ago
For your query you may find detailed solution.

https://www.c-sharpcorner.com/blogs/sql-server-union-vs-union-all-which-is-better-for-performance
+3
Laxmidhar Sahoo8y ago
Both UNION and UNION ALL clauses combine rows from result sets into a single result set. The UNIONclause removes duplicate rows that exist, while the UNION ALL clause does not.

Because the UNION ALL clause does not remove duplicate rows, it processes faster than the UNIONclause.
0