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.
- UNION ALL operator allows duplicate values .
- All select statement within UNION ALL must have the same number of columns.
- The columns must have similar data types.
- The columns order must be same the same in each SELECT statement.
SELECT column1, column2,.. FROM table_name1 UNION ALL SELECT column1, column2,.. FROM table_name2
Lets look at an example of UNION in SQL Server.
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
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
SELECT Name, Code from tblB