SQL UNION in SQL

UNION will return distinct records but Union all will return duplicate values also.

Check this example taken from
http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/


DECLARE @Table1 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table1
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
/* Declare Second Table */
DECLARE @Table2 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table2
SELECT 'First'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fifth'

SELECT *
FROM @Table1
UNION ALL
SELECT *
FROM @Table2
/* UNION */
SELECT *
FROM @Table1
UNION
SELECT *
FROM @Table2

If you run this you will get output

First
Second
Third
Fourth
Fifth
First
Third
Fifth


The above is for UNION ALL, you can see First third and Fifth are repeating.

Fifth
First
Fourth
Second
Third

This is output for second one which uses UNION. Distinct values are there