SQL UNION & UNION ALL Operators


Union Operator:

Union is used to combine or merge the results from two or more tables.

If we need to get data from two or more tables as one table and without duplicates, we can use this union operator.

This union operator will combine or merge the distinct records i.e. it will remove the duplicate records

union operator is little slower in performance as it is doing null check

Union All Operator:

Union all is used to combine or merge the results from two or more tables same as union operator but it will includes duplicate records also. i.e. it will fetch all records without any duplicate check

If we need to get data from two or more tables as one table and with duplicate data we can use this union all operator.

This union all operator will combine or merge all records i.e. it will not check for duplicate records.

union all operator is faster in performance as it will not do any null check

Union and Union All Example:

Table 1: Employees

select PostalCode from Employees

is having two records

  1. 111222
  2. 111333

Table 2: Customers

select PostalCode from Customers

is having two records

  1. 111222
  2. 111444

The above two tables are having one duplicate record.

If we do Union on the above two tables we will get the 
distinct combined results

select PostalCode from Customers
union
select PostalCode from Employees
Results:
1. 111222
2. 111333
3. 111444

If we do Union all on the above two tables we will get 
the just combined results with duplicates

select PostalCode from Customers
union all
select PostalCode from Employees
Results:
1. 111222
2. 111222
3. 111333
4. 111444

Happy coding

 

One thought on “SQL UNION & UNION ALL Operators

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s