Union/Union All in DBMS

Union/Union All in DBMS

In this article, we will learn about Union/Union All in DBMS.

UNION clause used to combine the result-set of two or more select queries

UNION Syntax

SELECT column_name(s) FROM table1
UNION/UNION ALL
SELECT column_name(s) FROM table2;

The columns used in all the select statements must have the following

  • the same number of columns
  • Similar or compatible data types
  • same logical order
Union/Union All in DBMS

Customers table:

CityCountry
ThimpuGermany
HyderabadIndia
HyderabadIndia

Suppliers table:

CityCountry
LondonUK
CaliforniaUSA
TexasUSA

Example for UNION/UNION All in DBMS

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Output:

City
Texas
Thimpu
London
Hyderabad
California

 

 Another Example for UNION/UNION All in DBMS

Selecting more than one column in the select query

Whenever more than one column is specified in the select clause then the combination of  all the columns considered that is if both the values in the row are same then only this considered as a unique value

SELECT City, Country FROM Customers
UNION
SELECT City, Country FROM Suppliers

Output:

CityCountry
TexasUSA
ThimpuGermany
LondonaUK
HyderabadIndia
CaliforniaUSA

 

UNION all in SQL

The only difference between UNION and UNION all Clause is that UNION removes duplicate values while comparing where UNION ALL allows duplicate values while combining

Example for UNION ALL

SELECT City, Country FROM Customers;
UNION ALL
SELECT City, Country FROM Suppliers

Output:

CityCountry
ThimpuGermany
HyderabadIndia
HyderabadIndia
LondonaUK
CaliforniaUSA
TexasUSA