TSQL - What is Difference Between Union and Union All Operator In TSQL

"Union" and "Union All" Operators combine the result set of two or more queries. The operators can be only applied to the result sets which return same number of columns as well the data types for the columns match.


As both Operators (Union and Union All ) combines the result sets, but there is a small difference between them

Union returns the distinct records from result sets.
Union All returns everything from results sets.


Let's create couple of tables for practice and see how these operators work.

USE TestDB
GO

CREATE TABLE dbo.Customer_1
  (
     Id    INT,
     FName VARCHAR(50),
     LName VARCHAR(100)
  )

GO

INSERT INTO dbo.Customer_1

VALUES      (1,
             'Aamir',
             'Shahzad'),
            (2,
             'Robert',
             'Ladson'),
            (3,
             'John',
             'Rivers') 
             
             CREATE TABLE dbo.Customer_2
  (
     Id    INT,
     FName VARCHAR(50),
     LName VARCHAR(100)
  )

GO

INSERT INTO dbo.Customer_2

VALUES      (1,
             'Aamir',
             'Shahzad'),
            (2,
             'Robert',
             'Jason'),
            (3,
             'John',
             'Rivers')

Run the queries with Union operator and we can see that it returned us distinct records from two result sets.

SELECT Id,FName,LName FROM dbo.Customer_1

UNION
SELECT Id,FName,LName FROM dbo.Customer_2

Fig 1 : Using Union Operator in TSQL

Execute the same queries by using Union All. You will get all the records from both result sets.

SELECT Id,FName,LName FROM dbo.Customer_1

UNION ALL

SELECT Id,FName,LName FROM dbo.Customer_2

Fig 2: Union All Operator in TSQL

Video Demo for "Union and Union All Operator in SQL Server"