What
are the differences between UNION and UNION ALL [UNION VS UNION ALL]?
|
UNION
|
UNION ALL
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Use
|
UNION
is used to combine multiple result sets into one result set and
will remove any duplicates rows that exist after combining the result sets.
|
UNION
ALL is used to combine multiple result sets into one result
set, but it does not remove any duplicate rows.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Performance
|
UNION
operation is slower than UNION ALL operation due to following course of
outputs.
UNION
is performing a DISTINCT operation across all columns in the result set and this removes the duplicate rows.
Union statement not only removes duplicate rows, but output is the sorted list.
|
UNION
ALL operation is faster than UNION ALL operation due to following course of
outputs.
UNION
ALL does not remove duplicate rows and does not sort the result.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Constraints
|
All the queries (result sets) which need to
combine need to have the same number of columns; columns 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
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Examples
|
CREATE TABLE dbo.Customers
( CustomerID int,CustomerName Varchar(50),
City Varchar(50))
GO
INSERT INTO dbo.Customers
VALUES(1,'Govinda Raj','Coimbatore'),
(2,'Krishna
Raj','Mangalore')
GO
CREATE TABLE dbo.Suppliers
( SupplierId int,SupplierName varchar(50),
City Varchar(50))
GO
INSERT INTO dbo.Suppliers VALUES
(1,'Govinda
Raj','Coimbatore'),
(2,'Durga
Devi','Chennai'),
(3,'Krishna
Raj','Bangalore'),
(4,'Rama
Krishnan','Bangalore')
GO
EXAMPLE 1:
SELECT * FROM
dbo.Customers WITH(NOLOCK)
UNION
SELECT * FROM
dbo.Suppliers WITH(NOLOCK)
GO
EXAMPLE 2:
SELECT * FROM
dbo.Customers WITH(NOLOCK)
UNION ALL
SELECT * FROM
dbo.Suppliers WITH(NOLOCK)
GO
EXAMPLE 3:
SELECT * FROM
(
SELECT CustomerName FROM dbo.Customers WITH(NOLOCK)
UNION
SELECT SupplierName FROM dbo.Suppliers WITH(NOLOCK)
) A ORDER BY CustomerName
EXAMPLE 4:
SELECT * FROM
(
SELECT CustomerName AS VendorName, City As
VendorCity FROM dbo.Customers
WITH(NOLOCK)
UNION
SELECT SupplierName, City FROM dbo.Suppliers WITH(NOLOCK)
) Vendor
EXAMPLE 5:
SELECT * FROM
(
SELECT CustomerName AS VendorName, City As
VendorCity FROM dbo.Customers
WITH(NOLOCK)
UNION ALL
SELECT SupplierName, City FROM dbo.Suppliers WITH(NOLOCK)
) Vendor
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Excellent article and with lots of information. I really learned a lot here. Do share more like this.
ReplyDeleteBlue Prism Training in Chennai
Blue Prism Online Training
Blue Prism Course in Chennai