AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

SQL Union – A Comprehensive Guide on the UNION Operator

Published on Oct 14,2019 71 Views
16 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

While handling data in databases, we often tend to use the set operators in SQL, to retrieve data according to our requirements by combining two or more SELECT statements. In this article on SQL UNION, I will discuss the UNION operator used to retrieve data in the following sequence:

Let us get started!

What is the SQL UNION operator? 

As the name suggests, this operator/ clause is used to combine the results of two or more SELECT statements. Here each SELECT statement used within the UNION statement must have the same number of columns in the same order. Also, all the columns present in the SELECT statements must have similar data types.

The UNION clause gives only unique values as output. Just in case, you want duplicate values, then you have to use the UNION ALL clause.

Moving on in this article on SQL UNION, let us understand the syntax.

SQL UNION Syntax

UNION Syntax

SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1
UNION
SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2;

UNION ALL Syntax

SELECT Column1, Column2, Column3, ..., ColumnN FROM Table1
UNION ALL
SELECT Column1, Column2, Column3, ..., ColumnN FROM Table2;

Moving on in this article, let us understand the differences between UNION and UNION ALL.

 

 

Differences between SQL UNION and UNION ALL

 

UNIONUNION ALL
Combines two or more result set and does not keep the duplicate values.Combines two or more result set and keeps the duplicate values.
Syntax: UNION
Syntax: UNION ALL
SQL UNION -SQL UNION -EdurekaSQL UNION ALL-SQL UNION -Edureka

Next, in this article on SQL UNION, let us understand the different ways to use this operator.

Examples of SQL UNION and UNION ALL

For your better understanding, I will be considering the following tables to show you different examples.

Employee Table

EmpIDNameEmpAgeCityPostalCodeCountry
1Emma23Berlin12109Germany
2Rahul26Mumbai400015India
3Aayra24New York10014USA
4John32LondonE1 7AEUK
5Derek29New York10012USA

Projects Table

ProjectIDNameWorkingDaysCityPostalCodeCountry
1Project 110Berlin12109Germany
2Project 27Mumbai400015India
3Project 320Delhi110006India
4Project 415Mumbai400015India
5Project 528Berlin12109Germany

Let us get started with examples.

SQL UNION Examples

UNION Operator Example

Write a query to retrieve distinct cities from the Employees and Projects table.

SELECT City FROM Employees
UNION
SELECT City FROM Projects
ORDER BY City;

Output:

City

Berlin

Delhi

London

Mumbai

New York

UNION ALL Operator Example

Write a query to retrieve cities from the Employees and Projects table. Here, duplicate values must be included.

 
SELECT City FROM Employees
UNION ALL
SELECT City FROM Projects
ORDER BY City; 

Output:

City

Berlin

Berlin

Berlin

Delhi

London

Mumbai

Mumbai

Mumbai

New York

New York

Next in this article, let us understand how to use the UNION clause with SQL Aliases.

UNION with SQL Aliases

SQL Aliases are used to give a table or a column a temporary name. So, let us write a query to list all the unique employees and projects.

SELECT 'Employee' AS Type, Name, City, Country
FROM Employees
UNION
SELECT 'Project', Name, City, Country
FROM Projects;

Output:

TypeNameCityCountry
EmployeeEmmaBerlinGermany
EmployeeRahulMumbaiIndia
EmployeeAayraNew YorkUSA
EmployeeJohnLondonUK
EmployeeDerekNew YorkUSA
ProjectProject 1BerlinGermany
ProjectProject 2MumbaiIndia
ProjectProject 3DelhiIndia
ProjectProject 4MumbaiIndia
ProjectProject 5BerlinGermany

UNION with WHERE clause

Write a query to retrieve the distinct Indian cities and their postal codes from both the Employees and Projects table.

SELECT City, PostalCode, Country FROM Employees
WHERE Country='India'
UNION
SELECT City, PostalCode, Country FROM Projects
WHERE Country='India'
ORDER BY City;

Output:

CityPostalCodeCountry
Delhi110006India
Mumbai400015India

UNION ALL with WHERE clause

Write a query to retrieve Indian cities and their postal codes from both the Employees and Projects table, where duplicate values are allowed

SELECT City, PostalCode, Country FROM Employees
WHERE Country='India'
UNION ALL
SELECT City, PostalCode, Country FROM Projects
WHERE Country='India'
ORDER BY City;

Output:

CityPostalCodeCountry
Delhi110006India
Mumbai400015India
Mumbai400015India
Mumbai400015India

Moving forward in this article, let us understand how to use the UNION and UNION ALL clauses with JOINS. JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables. 

UNION with JOINS

The SQL UNION operator can be used with SQL JOINS to retrieve data from two different tables. I am going to consider the following table along with the Employees table for the example.

ProjectDetails table

PIDWorkingDaysEmpIDCostforProject
1112420000
2216335000
3330160000
4425345000
5521150000
SELECT  EmpID, Name, CostforProject
   FROM Employees
   LEFT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID
UNION
   SELECT  EmpID, Name, CostforProject
   FROM Employees
   RIGHT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID;

Output:

EmpIDNameCostforProject
1Emma60000
1Emma50000
2RahulNULL
3Aayra35000
3Aayra45000
4John20000
5DerekNULL

UNION ALL with JOINS

Write a query to retrieve the EmpID, Name and CostforProject from Employees and ProjectDetails table, where duplicate values are allowed.

SELECT  EmpID, Name, CostforProject
   FROM Employees
   LEFT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID
UNION ALL
   SELECT  EmpID, Name, CostforProject
   FROM Employees
   RIGHT JOIN ProjectDetails
   ON Employees.EmpID = ProjectDetails.EmpID;

Output:

EmpIDNameCostforProject
1Emma60000
1Emma50000
2RahulNULL
3Aayra35000
3Aayra45000
4John20000
5DerekNULL
4John20000
3Aayra35000
1Emma60000
3Aayra35000
1Emma50000

By this, I come to the end of this article on SQL UNION. I hope you enjoyed reading this article on SQL UNION. We have seen the different ways to use the UNION  and UNION ALL commands to help you write queries. If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”SQL UNION” and I will get back to you.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.