Union/Union All in DBMS
Union in DBMS
Union clause used to combine the result-set of two or more select queries.
In this article, we will learn about Union/Union All in DBMS.
Union/Union All in DBMS
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
Customers table:
| City | Country |
|---|---|
| Thimpu | Germany |
| Hyderabad | India |
| Hyderabad | India |
Suppliers table:
| City | Country |
|---|---|
| London | UK |
| California | USA |
| Texas | USA |
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 valueSELECT City, Country FROM Customers UNION SELECT City, Country FROM Suppliers
Output:
| City | Country |
|---|---|
| Texas | USA |
| Thimpu | Germany |
| Londona | UK |
| Hyderabad | India |
| California | USA |
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:
| City | Country |
|---|---|
| Thimpu | Germany |
| Hyderabad | India |
| Hyderabad | India |
| Londona | UK |
| California | USA |
| Texas | USA |
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
Login/Signup to comment