UNION AND UNION ALL OPERATORS

Union and union all is in sql server used to combining the result sets of two or more select statements.

I have created the two tables with the following data

Create Student_A Table

CREATE TABLE Student_A

(

 ID INT IDENTITY(1,1),

 NAME VARCHAR(10),

 MARKS INT

)

Inserting the data into Student_A table

INSERT INTO Student_A(NAME,MARKS) VALUES('Dave',500)

INSERT INTO Student_A(NAME,MARKS) VALUES('Rakesh',400)

INSERT INTO Student_A(NAME,MARKS) VALUES('Ramu',300)

Create Student_B Table

 CREATE TABLE Student_B

(

 ID INT IDENTITY(1,1),

 NAME VARCHAR(10),

 MARKS INT

)

Inserting the data into Student_B table

INSERT INTO Student_B(NAME,MARKS) VALUES('Dave',500)

INSERT INTO Student_B(NAME,MARKS) VALUES('Raju',300)

INSERT INTO Student_B(NAME,MARKS) VALUES('Nani',350)

Selecting two table data

SELECT * FROM Student_A

SELECT * FROM Student_B

When we run above select statements the result in two result sets because of these are two separate select statements.

But we want both results in single result set this can done by using  union or union all operators.     

UINION ALL:

It combines the both result sets but it does not select the distinct data and also it does not sort the data.

SELECT * FROM Student_A

UNION ALL

SELECT * FROM Student_B

When we observe the above result set it combines the Student_A data with Student_B data.

In Union all operator does not distinct the data and data also not in sorted order. 

UINION:

It combines the both result sets but it selects the distinct data and also it sorts the data.

Performance

Union all faster than the union because of union all does not selects the distinct and also data is not in sorted order.

We can check union and union all operators performance test by using execution query plan

SELECT * FROM Student_A

UNION ALL

SELECT * FROM Student_B

Run above query

Press (Ctrl+L) To Check execution query plan

It directly combines both result sets .it does not sorts the data and does not select's the distinct data because cost will be 0%

SELECT * FROM Student_A

UNION

SELECT * FROM Student_B

Press  (Ctrl+L) To Check execution query plan

Note: Union and union all operators in select statement number of columns of all selects statements should be same and order of types should be same.

Senario-1

SELECT ID,NAME FROM Student_A

UNION ALL

SELECT ID,NAME,MARKS FROM Student_B

In above first select satement selects the ID,Name colums from Student_A table (2 colums).In second select statement selects the ID,Name,MARKS from Student_B table (3 colums).when we run above query command will be fail beacause number of colums should mathch both tables .

Msg 205, Level 16, State 1, Line 3

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Senario-2

SELECT NAME,ID FROM Student_A

UNION ALL

SELECT ID,NAME FROM Student_B

The above query will fail because of order of data type should be match.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'Dave' to data type int.

Differences between union and union all 

UNION	UNION ALL
No Duplicate when combing the two or more result set	Duplicate data will available when combing the two or more result sets
Data in sorted order	Data not in sorted order
Performance wise union will  not  faster than union all Because it selects the distinct data and then sorts the data order	Performance wise union all will faster than union