Union and UnionAll in SQl server

UNION and UNION ALL operators in SQL Server, are basically used to combine the result-set of two or more SELECT queries.

Following are the constraints for using UNION/UNION ALL Operator:

§ All the query’s which need to combine need to have the same number of columns

§ Column should be of the same data type/compatible data types

§ ORDER BY clauses can only be issued for the overall result set and not within each result set

§ Column names of the final result set will be from the first query

Lets understand the union and union All with an example

Run the following sql script where we are creating the a DataBase Named:DotnetOffice. Then in this database we are creating two tables Collage and Student .

CREATE DATABASE DotNetOffice

USE DotNetOffice

CREATE TABLE dbo.Collage

(CollageID int,CollageName Varchar(50),CollageCity Varchar(50))

INSERT INTO dbo.Collage

VALUES(1,'VIT','Bangalore'),

(2,'NIT','Mysore')

Create Table dbo.Student

(StudentId int,StudentName varchar(50),StudentCity Varchar(50))

INSERT INTO dbo.Student VALUES(1,'Munesh sharma','Bangalore'),

(2,'Rahul','Jaipur'),

(3,'Anshuman','Jaipur')

SELECT * FROM dbo.Collage WITH(NOLOCK)

SELECT * FROM dbo.Student WITH(NOLOCK)

Above SQL Script will create dataTable with data.

Example 1

1. Use of Union and Union All with above 2 tables for that Run the following Sql script and see the result.

-- Union

SELECT CollageCity FROM dbo.Collage WITH(NOLOCK)

union

SELECT StudentCity FROM dbo.Student WITH(NOLOCK)

--Union All

SELECT CollageCity FROM dbo.Collage WITH(NOLOCK)

union ALL

SELECT StudentCity FROM dbo.Student WITH(NOLOCK)

And output will be

Differences between UNION and UNION ALL (Common Interview Question)
From the output, it is very clear that, UNION removes duplicate rows, where as UNION ALL does not. When use UNION, to remove the duplicate rows, sql server has to to do a distinct sort, which is time consuming. For this reason, UNION ALL is much faster than UNION.

Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.

Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same.

Example-2

MisMatch in the No of Columns in the select queries combined by the UNION Operator:

Run the following Script and see the output

SELECT CollageName,CollageCity

FROM DBO.Collage WITH(NOLOCK)

UNION

SELECT *

FROM DBO.Student WITH(NOLOCK)

Reason for the above error is:First statement of the UNION has two columns in the select list where as Second statement has 3 columns (i.e. * means all the columns in the Collage table)

Example-3

No of columns matching but mismatch in the data type of the columns

Run the following Script and see the output

SELECT CollageID

FROM DBO.Collage WITH(NOLOCK)

UNION

SELECT StudentName

FROM Student WITH(NOLOCK)

Reason for this error is: first column in the first statement of the UNION is of type INT and the first column in the Second statement is VACHAR, due to this incompatible data type we are seeing an error here.

Select Column With TypeCast

Now we can re-write this query to work as below. Also by seeing the column name in the result we can conclude that the column names in the result are always taken from the first statement of the UNION clause.

Run the following Script and see the output

SELECT CAST(CollageID AS VARCHAR(50)) As ClgIDInchar,CollageCity

FROM DBO.Collage WITH(NOLOCK)

UNION

SELECT StudentName,StudentCity

FROM Student WITH(NOLOCK)

Difference between JOIN and UNION
JOINS and UNIONS are different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, where as JOINS, retrieve data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.