SQL UNION ALL

The UNION ALL operator, similar to UNION, is used to combine the result sets of multiple SELECT statements into a single result set. 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

Lets look at an example of  UNION in SQL Server.

First, 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 as shown below.

SELECT * FROM tblA

SELECT * FROM tblB

Using SQL Union ALL in Select Statement

Following SQL UNION ALL statement returns the duplicate values also from tblA and tblB unlike UNION which removes duplicates, and this is the only difference between UNION and UNION ALL.

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 that is ‘JOY‘ and ‘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 .

UNION ALL on tables when select statement have different column data type 

SELECT sno, code FROM tblA

UNION ALL

SELECT Name, Code FROM tblB

As you can see in first select statement, the very first column is Sno that is of Int datatype while in second select statement, the very first column is Name that is of Varchar datatype.

So in such case, UNION ALL operator returns a conversion failed error as shown in below screenshot.

UNION ALL on tables when select statement have different number of columns

SELECT Sno, Name, Code FROM tblA

UNION ALL

SELECT Sno, Code FROM tblB

 

As you can see, both select statement have different number of columns , first select statement have three columns while second select statement have two columns only. Therefore, it returns an error that you can see in above screenshot.

Therefore, it returns an error that you we can see above.

UNION ALL with Order By clause

You can also sort the UNION ALL result using Order by in select statement .

Following statement uses union all to combine the result of both select statement also sort the result based on Name in descending order.

SELECT Name, Code FROM tblA

UNION ALL

SELECT Name, Code from tblB

ORDER BY NAME DESC

Note that, Order by clause are always used with the last Select Statement in the UNION ALL, using it in first statement or somewhere else before the last select statement, it gives an error as shown below.

In following Statement we are using Order by with first select statement, and you can see it gives an error.

SELECT Name, Code FROM tblA

ORDER BY Name DESC

UNION ALL

SELECT Name, Code from tblB

 

Also Read..

SQL SERVER INTERSECT

SQL SERVER EXCEPT

SQL SERVER UNION