Union vs Union ALL in SQL Server

Description

In this article, I am going write the difference between Union vs Union All in SQL Server in multiple rows insert statement.

Union vs Union All in SQL

UNION ALL – It will not remove duplicate rows when you insert multiple rows or values by using Union All, it just combine all the rows.

UNION – Removes duplicate rows when you insert multiple rows or records by using Union.

Union ALL in SQL to Insert multiple rows or records

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into SQL Server using UNION ALL
INSERT INTO #tempTable (ID, UserName) 
Select 1, 'User1'
UNION ALL
Select 2, 'User2'
UNION ALL
Select 2, 'User2'
UNION ALL
Select 3, 'User3'

--Select inserted values from temp table 
Select * From #tempTable

Union in SQL to Insert multiple records/rows

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into SQL Server using UNION
INSERT INTO #tempTable (ID, UserName) 
Select 1, 'User1'
UNION 
Select 2, 'User2'
UNION 
Select 2, 'User2'
UNION 
Select 3, 'User3'

--Select inserted values from temp table 
Select * From #tempTable

Note: If you have no problem with duplicate rows, you can use Union All instead of Union for better performance. since Union query will check all the rows to find duplicates, it will takes more execution time compared with Union ALL.

Thanks,
Morgan
Software Developer