UNION vs UNION ALL

UNION or UNION ALL is one of famous clause can be used in SQL Server to join 2 or more query to be one result. But, the point is what is the differences between UNION and UNION ALL ?.

Please kindly execute below query:

[code language=”sql” title=”Sample UNION & UNION ALL”]
— sample UNION
SELECT ‘arif’, 1
UNION
SELECT ‘arif’, 2

— sample UNION ALL
SELECT ‘arif’, 1
UNION ALL
SELECT ‘arif’, 2
[/code]

The result both is same, but please kindly execute below query:

[code language=”sql” title=”Sample UNION & UNION ALL”]
— sample UNION
SELECT ‘arif’, 1
UNION
SELECT ‘arif’, 1

— sample UNION ALL
SELECT ‘arif’, 1
UNION ALL
SELECT ‘arif’, 1
[/code]

The result is different.

My conclusion is UNION can join 2 or more query with avoiding duplicate value whereas UNION ALL doesn’t. If you have better or deeper explanation, please kindly contact me.