SQL UNION operator is used to combine the result sets of two or more select statements
The UNION operator selects only distinct values by default.
- All select statement within UNION 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 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') CREATE TABLE tblB ( Sno INT NOT NULL, Name VARCHAR(30), Code VARCHAR(15) ) 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 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, Name and Code values for sno =1 are common in both tables so union returns only one unique row for these records in result set.
Union on tables when select statement have different column data type
SELECT sno, code FROM tblA UNION 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 operator returns a conversion failed error as shown in below screenshot.
UNION on tables when select statement have different number of columns
SELECT Sno, Name , Code FROM tblA UNION 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.
UNION with Order By clause
You can also sort the union result using Order by in select statement .
Following statement uses union to combine the result of both select statement also sort the result based on Name in descending order.
SELECT Name, Code FROM tblA UNION 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, 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 SELECT Name, Code From tblB
Recommended for you
1,178 total views, 2 views today