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
GO
USE DotNetOffice
GO
CREATE TABLE dbo.Collage
(CollageID int,CollageName Varchar(50),CollageCity Varchar(50))
GO
INSERT INTO dbo.Collage
VALUES(1,'VIT','Bangalore'),
(2,'NIT','Mysore')
GO
Create Table dbo.Student
(StudentId int,StudentName varchar(50),StudentCity Varchar(50))
GO
INSERT INTO dbo.Student VALUES(1,'Munesh sharma','Bangalore'),
(2,'Rahul','Jaipur'),
(3,'Anshuman','Jaipur')
GO
SELECT * FROM dbo.Collage WITH(NOLOCK)
SELECT * FROM dbo.Student WITH(NOLOCK)
GO
|
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.
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.
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.
1 comments:
Write commentsgreat
ReplyEmoticonEmoticon