Table of contents

Introduction

UNION clause

UNION ALL clause

UNION & UNION ALL clause with WHERE clause

Frequently Asked Questions

Key Takeaways

Last Updated: Mar 27, 2024

Unions Clause

Author Jainish Patel

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

Consider a situation where you have two different tables, and your task is to merge both the tables and make them into a single table. In a database, terminology merge is referred to as a union.

Source: imgflip

To solve this task, let’s learn an amazing Clause named UNION.

In this article, we will be analysing how to use the UNION clause. The UNION clause will help merge two or more tables into a single table.

All the queries and questions apply to the following Database Tables.

Consider the FacebookData table and LinkedInData table having all the data of users consisting of columns like PersonID, Name, City, ContactNumber.

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

UNION clause

The Union clause combines two or more tables without duplicate data in the final table. Union clause can only be applied with the SELECT statement.

Before moving forward, Let’s look at the conditions for which the UNION clause can be applied.

The number of columns should be the same for the tables a user wants to merge.
The data type of the columns should be the same. If the column data type is INT in one table, the table the user wants to merge should be INT data type, although the column names do not have to be identical.
The occurrence of columns in both tables should be in the same order. If table1 has the following columns of data type INT, varchar, INT, then in the second table, the data type of the columns should be of the same order.

The above conditions should be checked before applying the UNION clause. But, there are no restrictions with the entries of the rows in both the tables.

The above image shows how the UNION clause will work internally.

Even though the number of columns and data types are the same in the above table, but not in the same order, the UNION clause will not be applied.

Syntax

SELECT column_name(s) FROM tableName1

UNION

SELECT column_name(s) FROM tableName2;

Let consider an example,

From the given two tables named as FacebookData and LinkedInData. We need to find the union of both the tables and print the resultant table.

SQL Query:

SELECT * FROM FacebookData
UNION
SELECT * FROM LinkedInData;

Result for the above query:

Let consider another example,

From the given two tables, FacebookData and LinkedInData, we need to find the union of both the tables and print only the City column in the resultant table.

SQL Query:

SELECT City FROM FacebookData
UNION
SELECT City FROM LinkedInData;

Result for the above query:

After looking at the UNION clause, one most common question that comes to mind is that we know that many people have the same name, live in the same city, and have the same contact number because they have a different service provider. So, there may be situations where you want duplicate data entries also.

The UNION ALL clause can be used to solve this type of problem.

UNION ALL clause

This clause provides all the functionality and features of the UNION clause. In addition to that, it retains duplicate data entries also.

The above image shows how the UNION clause will work internally.

Syntax

SELECT column_name(s) FROM tableName1
UNION ALL
SELECT column_name(s) FROM tableName2;

Let consider an example,

From the given two tables FacebookData and LinkedInData. We need to find the union of both the tables and print the resultant table, Don’t remove duplicate entries.

SQL Query:

SELECT * FROM FacebookData
UNION ALL
SELECT * FROM LinkedInData;

Result for the above query:

UNION & UNION ALL clause with WHERE clause

Where clause will help find the union of two or more tables following the same conditions, we can use the AND & OR clause with the WHERE clause.

Syntax

SELECT column_name(s) FROM tableName1
WHERE [conditions]
UNION
SELECT column_name(s) FROM tableName2
WHERE [conditions];

Let consider an example,

From the given two tables FacebookData and LinkedInData. We need to find the union of both the tables where Name is equal to Krishna. Print the resultant table.

SQL Query:

SELECT * FROM FacebookData
WHERE Name='Krishna'
UNION
SELECT * FROM LinkedInData
WHERE Name='Krishna';

Result for the above query:

How the query will work on both tables.

The where clause will apply to each table to find out the rows which satisfy the condition. After that, the Union clause will apply to the rows that satisfy the conditions. As Name equal to Krishna is present in both the tables, the duplicate row will be removed, and only 1 row will be printed in the output.

Let consider another,

From the given two tables FacebookData and LinkedInData. We need to find the union of both the tables where Name is equal to Dhurv in the FacebookData table and Sunny in the LinkedInData table. Print the resultant table.

SQL Query:

SELECT * FROM FacebookData
WHERE Name='Dhruv'
UNION
SELECT * FROM LinkedInData
WHERE Name='Sunny';

Result for the above query:

Let consider another,

We need to find the union of both the tables where Name is equal to Sunny or City is equal to Surat in FacebookData table and City is equal to Pune or Ahmedabad in LinkedInData table. Print the resultant table. Don’t remove duplicate entries.

SQL Query:

SELECT * FROM FacebookData
WHERE Name='Sunny' OR City='Surat'
UNION ALL
SELECT * FROM LinkedInData
WHERE City='Pune' OR City='Ahmedabad';

Result for the above query:

How to use the ORDER BY clause with the UNION and UNION ALL clause?

Let consider an example,

From the given two tables FacebookData and LinkedInData. We need to find the union of both the tables and print the resultant table Sorted by City in descending order.

SQL Query:

SELECT * FROM FacebookData
UNION
SELECT * FROM LinkedInData
ORDER BY City DESC;

Result for the above query:

Another variation of the question is to find union both the table and include duplicate entries of data and print the resultant table Sorted by City in ascending order.

SQL Query:

SELECT * FROM FacebookData
UNION ALL
SELECT * FROM LinkedInData
ORDER BY City ASC;

Result for the above query:

Frequently Asked Questions

1. What is the main difference between the UNION clause and the UNION ALL clause?

The only difference between UNION and UNION ALL is that Union extracts the rows specified in the query. In contrast, Union All extracts all the rows, including the duplicates (repeated values) from both the queries.

2. What are all the clauses that the UNION clause works with?

There are many clauses with which the UNION clause can apply in a query. But WHERE clause, ORDER BY clause, AND & OR clause are frequently used.

3. Compare UNION and UNION ALL performance in terms of time taken to perform the task.

Both clauses combine rows from result sets into a single result table. The UNION clause removes duplicate rows, whereas the UNION ALL clause keeps the same rows. Because the UNION ALL clause does not remove duplicate rows, it runs faster than the UNION operator.

Key Takeaways

This article discusses how to use the UNION Clause using various examples and write SQL queries. We also used the UNION clause with WHERE, ORDER BY, and many other combinations of clauses. Also, learn when to use these clauses based on queries asked.

Check out this problem - Duplicate Subtree In Binary Tree

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS.

Live masterclass

Interview-Ready Excel & AI Skills for Microsoft Analyst Roles

by Prerita Agarwal

19 Jun, 2025

01:30 PM

238+ registered

AI PDF Analyzer using FastAPI – Explained by Google SWE3

by Akash Aggarwal

16 Jun, 2025

01:30 PM

194+ registered

Amazon Data Analyst: Advanced Excel & AI Interview Tips

by Megna Roy

17 Jun, 2025

01:30 PM

2180+ registered

From Full Stack to AI Stack: What Modern Web Dev Looks Like

by Shantanu Shubham

18 Jun, 2025

01:30 PM

104+ registered

Interview-Ready Excel & AI Skills for Microsoft Analyst Roles

by Prerita Agarwal

19 Jun, 2025

01:30 PM

238+ registered

AI PDF Analyzer using FastAPI – Explained by Google SWE3

by Akash Aggarwal

16 Jun, 2025

01:30 PM

194+ registered

View more events