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.


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


/*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 tables.

PaymentMethods Table
Unused_paymentMethods Table

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

Something that we notice is that we only have one “Debit-Card” row once instead of three times (this row was located on both tables) and one “Paypal” row (this row was located on a single table duplicated). UNION clause returns unique elements from the union of two or more tables; it is like using a DISTINCT clause after combining both tables.

If we use UNION ALL, we get the following result.

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 *