SQL UNION

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.

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

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

 

Also Read..

SQL SERVER INTERSECT

SQL SERVER EXCEPT

SQL SERVER UNION ALL