Home » SQL UNION ALL

SQL UNION ALL

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




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

SELECT * FROM tblA

SELECT * FROM tblB

Using SQL Union 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 statment 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 satement 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 statment 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

 




Recommended for you

SQL SERVER INTERSECT

SQL SERVER EXCEPT

SQL SERVER UNION




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.