Online Tutorials & Training Materials | STechies.com
Register Login

Difference between Union and Union all

|| || 0

Difference between Union and Union all
Stechies

What is UNION in SQL?

Union in SQL is a set operator that is used to combine the records from two or more tables. The main functionality of the operator is to remove the duplicate records from these tables. For every duplicate row in the tables, only one row is shown in the result.

Based on performance, the operator is not preferable as it is time-consuming while execution of the SELECT statements. But if the user requires only the unique records from both tables, then Union is the best choice.

Syntax of UNION Operator

SELECT <column1>, < column2>, < column n> FROM table1

UNION

SELECT <column1>, < column2>, < column n> FROM table2

Example of UNION operator

Here we have two tables Supplier and Order.  Fetching data from both table using UNION key word and in the result produced all row except duplicate rows. Like supp_id  401 Ram and 403 Amit is removed from the result.

 Table supplier

Supp_id

Supp_ name

401

Ram

402

Shyam

403

Amit

table order

Order_id

Supp_ name

401

Ram

202

Mohit

403

Amit

SELECT supp_id, supp_name FROM supplier

UNION

SELECT order_id,  supp_name FROM order

And results will be :-

Supp_id

Supp_name

Order_id

401

Ram

401

402

Shyam

402

403

Amit

403

202

Mohit

202

What is UNION ALL in SQL?

Union All in SQL is a set operator that is used to combine records from two or more tables based on a set of Select statements. The operator does not remove the duplicate records from these tables during combination. Therefore, all the records are displayed as a result.

This operator is faster than Union as it does not remove duplicate records and executes the statements quickly. This is commonly used when the user does not want unique records but wants a combination of some tables.

Syntax of UNION ALL operator:-

SELECT <column1>, < column2>, < column n> FROM table1

UNION ALL

SELECT <column1>, < column2>, < column n> FROM table2

Example of UNION ALL

 In this example you see that all row are here displayed whether it is duplicate value.

 Table supplier

Supp_id

Supp_ name

401

Ram

402

Shyam

403

Amit

Table Order

Order_id

Supp_ name

401

Ram

202

Mohit

403

Amit

SELECT supp_id, supp_name FROM supplier

UNION ALL

SELECT order_id,  supp_name FROM order

And results will be :-

Supp_id

Supp_name

Order_id

401

Ram

401

402

Shyam

402

403

Amit

403

202

Mohit

202

401

Ram

401

403

Amit

403

Difference between Union and Union all

Union

Union All

This is a set operator that is used to fetch records from two tables and return a resultant set where the duplicate records are removed.

This is a set operator that is used to fetch records from two tables, but does not remove the duplicate records.

It performs a DISTINCT on the resultant set.

It does not perform a DISTINCT.

As it removes duplicates, the execution of the statement is slower than Union All.

Execution is faster than Union as the duplicates are removed.

Key Differences Between UNION ALL and UNION in SQL Server

The key differences between Union and Union All in SQL Server are:

  • Union All selects all the records from the tables according to the Select statement along with the duplicates. In Union, the duplicate records are removed.
  • Union all does not select the Distinct records from the tables. Union performs a Select Distinct on the resultant set and only displays unique records.
  • Execution of statements using Union All is faster. It is not as fast in Union as time is taken while removing the similar rows and columns.      


Related Articles