Online Tutorials & Training Materials | STechies.com
Register Login

Union vs Union All in SQL

|| || 0

Union vs Union All in SQL
Stechies

This tutorial aims to explain the difference between union and union all in SQL. Both the operators while executing a statement is quite the same whereas the arguments are the same but results will differ.

Difference between Union and Union all Comparision Chart

Points

UNION

UNION ALL

Performance

It is slower, as it check for  duplicate values

It is faster as it combines all values duplicate also.

But in case of sql server

Sort

It do distinct sort & removes duplicate row

It does not use distinct sort  produce all & duplicate also

Different data type

It needs similar data type. It cannot work with different data types & return an error

It works with text & numeric  data type and return all results

What is UNION in SQL?

Union is basically a set operator which is used to join two sets. In computer science, it is used to combines multiple query/statement into single results.

In SQL the union operator is used to combine two or more select statement of similar data type column of multiple tables. Where the duplicate row values are implicit/automatically removed/suppressed.

Union will produce distinct rows, it means it checks & remove a duplicate row in the process this performance reduced.

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

UNION ALL is also a set operator but allow duplicate values. The UNION ALL is a keyword with help of we can create either multiple queries from a single table or multiple queries from multiple tables to get meaning full result with duplicate values.

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

Key Difference Between UNION ALL and UNION in SQL Server

  1. In the UNION ALL it produces all rows along with duplicate values not in UNION.
  2. In UNION it suppresses the duplicate values, but not in UNION ALL.
  3. In UNION cannot work with two different data type, but in the UNION ALL it appends all along and produces it.
  4. Sometimes UNION is quite faster but in case of local data fetch UNION ALL is faster as it does not filter the duplicate values.

Summary

Here in summary of union and union all that we understand both are quite similar to on some point and different at some of execution. While executing a statement the arguments are quite same but results will differ for example both working with same data structure column, but both behave differently.


Related Articles