Search This Blog

Wednesday, July 24, 2019

Difference between union and union all

UNIONUNION ALL
UNION removes duplicate rows.“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.UNION ALL can work with all data type columns.

DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
  
INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')  
INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')  

INSERT INTO @Table2 VALUES(1,'Tejas', '88996655')  
INSERT INTO @Table2 VALUES(2,'Purvi', '99986655')  

SELECT * FROM @Table1  
UNION   
SELECT * FROM @Table2  

No comments :