Sql Server Union and Union all

Sql Server Union and Union all

Union is a very useful Sql command that enables you to combine the values of two or more tables. One prerequisite for using union is that those tables must contain the same amount of columns and data types must be consistent.


We have two different kinds of union commands

  • Union
  • Union All

For this example, we are going to use two tables. One is paymentMethods and the other one is unsed_paymentMethods.

1. Demo Data

/*Creating tables*/

CREATE TABLE [dbo].[paymentMethods](
[PaymentMethodName] [nvarchar](50) NULL,
[LastEditedBy] varchar(50) NULL,
[EditDate] date NULL
) ON [USERDATA]

CREATE TABLE [dbo].[unused_paymentMethods](
[PaymentMethodName] [nvarchar](50) NULL,
[LastEditedBy] varchar(50) NULL,
[EditDate] date NULL
) ON [USERDATA]

/*Inserting data into those tables*/

INSERT INTO paymentMethods(PaymentMethodName,LastEditedBy,EditDate)
values('Cash','Alvaro','20180202')
,('Credit-Card','Robert','20180401')
,('Debit-Card','John','20180601')
,('Debit-Card','John','20180601')

INSERT INTO unused_paymentMethods(PaymentMethodName,LastEditedBy,EditDate)
values
('Cash','Robert','20180401')
,('Debit-Card','John','20180601')
,('Paypal','Brandom','20180501')
,('Paypal','Brandom','20180501')

By using these queries we have created and fulfill all our PaymentMethods Table
Unused_paymentMethods Table

2. Sql Server Union

In this example we have two types of duplicated records, we have “Debit-Card” row duplicated in paymentMethods table and once in unsed_paymentmethods, as you can see this row is located in two different tables.Also we have “Paypal” Row duplicated twice in “unused_paymentMethods” table.By applying Union in our query, we have the following result

;
SELECT PaymentMethodName
,LastEditedBy
,EditDate
FROM paymentMethods

UNION 

SELECT PaymentMethodName
,LastEditedBy
,EditDate 
FROM unused_paymentMethods

3. Sql Server Union All

SELECT PaymentMethodName
,LastEditedBy
,EditDate
FROM paymentMethods

UNION ALL

SELECT PaymentMethodName
,LastEditedBy
,EditDate 
FROM unused_paymentMethods


As you can see, we have three repetitions of “Debit-Card” row and two of “Paypal” row. The main difference between UNION and UNION ALL is that UNION ALL does not apply a distinct on the result so it returns the union of result complete while UNION applies a distinct and returns the union of unique elements on the union of those tables.

I have an unpleasant experience while using UNION at job, It took me sometime to understand that UNION return distinct values from the whole union of tables and not for repeated values on both tables.If you do not need elements to be unique, try to use “UNION ALL” instead of “UNION”. This for performance reasons.


Leave a Reply

Your email address will not be published. Required fields are marked *