SQL UNION in SQL

Question

Hi Friends I want to know the difference between SQL UNION and SQL UNION ALL command in SQL ?

Santhosh Kumar Jayaraman · Answer

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