Difference between union and union all

The major difference between the UNION and UNION ALL operators in SQL is how they handle duplicate rows when the result set of multiple select statement is combined.

SQL UNION and UNION ALL operator is used to combine the result sets of two or more select statements.

The UNION operator selects only distinct values by default.

1. 1. All select statement within UNION must have the same number of columns.
   2. The columns must have similar data types.
   3. The columns order must be same  the same in each SELECT statement.

SYNTAX

SELECT column1, column2,.. FROM table_name1

UNION 

SELECT column1, column2,.. FROM table_name2

The UNION ALL operator, similar to UNION. However, unlike UNION, UNION ALL does not eliminate duplicate rows.

1. 1. UNION ALL operator allows duplicate values .
   2. All select statement within UNION ALL must have the same number of columns.
   3. The columns must have similar data types.
   4. The columns order must be same the same in each SELECT statement.

SYNTAX

SELECT column1, column2,.. FROM table_name1

 UNION ALL

 SELECT column1, column2,.. FROM table_name2

Let’s see the difference between UNION and UNION ALL with the help of an example.

First we Create a sample tables named as tblA and tblB and Insert some records into these tables.

CREATE TABLE tblA (
Sno INT NOT NULL,
Name VARCHAR(30),
Code VARCHAR(15)
)

INSERT INTO tblA (Sno, Name, Code) 
VALUES(1, 'JOY','JO_FTX'),
(2, 'RAHEJA', 'RAH_FTX'),
(3, 'JOHN D', 'JHN_FTX')

INSERT INTO tblB (SNO, NAME, CODE)
VALUES (1,'JOY','JO_FTX'),(2,'MARK JACK', 'MA_FTX')

Now we have a table tblA and tblB in SQL database as shown below.

SELECT * FROM tblA

SELECT * FROM tblB

Using SQL Union and UNION ALL in Select Statement

Following SQL statement returns the combine result set of both tables. It eliminates any duplicate rows between the two SELECT statements.

SELECT Name, Code FROM tblA
UNION
SELECT Name, Code FROM tblB

As you can see , row where Name =’JOY’ and Code = ‘JO_FTX’ are common in both tables so UNION returns only one unique row for these records in result set.

Now, we will apply UNION ALL operator in same query to see the difference.

SELECT Name, Code FROM tblA

UNION ALL

SELECT Name, Code FROM tblB

As you can see, in above result there are duplicate rows for Name and Code values where Name is ‘JOY‘ and Code is ‘JO_FTX’, first row is returned from first select statement while second row is returned from second select statement.

UNION ALL does not remove the duplicates, that why statement returns duplicate rows for name and code.

So, the conclusion is that UNION removes duplicate rows from the combined result set, while UNION ALL includes all rows, including duplicates. The choice between UNION and UNION ALL depends on the specific requirements of the query and whether duplicate rows should be included or eliminated in the final result set.

If we compare the performance of UNION and UNION ALL. UNION is slower then UNION ALL as it uses distinct sort operation to eliminate duplicates. UNION ALL is faster as it does not remove duplicates and selects all the rows from the involved tables

If you want to explore more about UNION and UNION ALL you can read below posts.

UNION 

UNION ALL

Also Read..

SQL Server Interview Questions and Answers