Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Difference Between UNION and UNION ALL
3.
What is UNION?
3.1.
Syntax
4.
What is UNION ALL?
4.1.
Syntax
5.
UNION Vs UNION ALL: Example
5.1.
UNION 
5.2.
SQL
5.3.
SQL
5.3.1.
Creating Table 2
5.4.
SQL
5.5.
SQL
5.6.
SQL
5.6.1.
Output
5.6.2.
Explanation
5.7.
UNION ALL
5.8.
SQL
5.8.1.
Output
5.8.2.
Explanation
6.
When Should You Use UNION and UNION ALL?
7.
Frequently Asked Questions
7.1.
Which is faster, UNION or UNION ALL?
7.2.
Does UNION ALL remove duplicates?
7.3.
What are the two main differences between structure and union?
7.4.
Do UNION and UNION ALL operate differently with NULL values?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference between UNION and UNION ALL

Author Surbhi Sharma
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In SQL, both UNION and UNION ALL are employed for fetching data from multiple tables. The UNION operation retrieves unique records from each table, whereas UNION ALL includes all records from both tables.

Difference between UNION and UNION ALL

Comparing UNION vs. UNION ALL reveals a key distinction:

  • UNION retrieves only unique records
     
  • UNION ALL retrieves all records, including duplicates
     

Also See - Union Operation

Difference Between UNION and UNION ALL

Parameter UNION UNION ALL
Definition It is used to combine the result sets of two or more SELECT queries into a single result set. It combines the result sets of multiple SELECT queries into a single result set.
Performance It involves additional processing to identify and eliminate duplicate rows from the result set that's why it slower. It is generally faster than UNION because it does not involve duplicate elimination.
Removal of Duplicates Duplicates are automatically removed from the combined result set. It does not remove duplicates.
Syntax SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is UNION?

The UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. It removes duplicates from the combined result set.

Syntax

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

What is UNION ALL?

The UNION ALL is used to combine the result sets of two or more SELECT queries into a single result set. It's used to retrieve data from multiple tables or queries and present them as a unified result. The key characteristic of UNION ALL is that it includes all rows from all the involved queries, including duplicates.

Syntax

SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;

UNION Vs UNION ALL: Example

UNION 

We have two tables named ‘Table 1’ and ‘Table 2’ with employee data as specified below:
Creating Table 1

empID

Name

dept

1

John

Sales

2

Mary

Marketing

3

Peter

Finance

4

Tom

Marketing

5

Lisa

Sales

6

Jane

HR

Code:

  • SQL

SQL

CREATE TABLE table1 (

empId INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);
creating table 1

Inserting Values into Table 1:

  • SQL

SQL

INSERT INTO table1 VALUES (1, 'John', 'Sales');

INSERT INTO table1 VALUES (2, 'Mary', 'Marketing');

INSERT INTO table1 VALUES (3, 'Peter', 'Finance');

INSERT INTO table1 VALUES (4, 'Tom', 'Marketing');

INSERT INTO table1 VALUES (5, 'Lisa', 'Sales');

INSERT INTO table1 VALUES (6, 'Jane', 'HR');
inserting values

Creating Table 2

empID

Name

dept

7

John

Sales

8

Tom

Marketing

9

Lisa

Sales

10

Jane

HR

11

Mary

Marketing

Code:

  • SQL

SQL

CREATE TABLE table2 (

empId INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);
Creating Table 2

Inserting Values into Table 2:

  • SQL

SQL

INSERT INTO table2 VALUES (7, 'John', 'Sales');

INSERT INTO table2 VALUES (8, 'Tom', 'Marketing');

INSERT INTO table2 VALUES (9, 'Lisa', 'Sales');

INSERT INTO table2 VALUES (10, 'Jane', 'HR');

INSERT INTO table2 VALUES (11, 'Mary', 'Marketing'); -- duplicate row
inserting values

Applying UNION to the data of Table 1 and Table 2:

  • SQL

SQL

SELECT name, dept FROM table1

UNION

SELECT name, dept FROM table2;

Output

output

Explanation

In the example above, we choose information from tables table1 and 2, which are two separate tables. From both tables, we select the columns "empID", "name", and "dept”. Then using UNION, we combine the data. All duplicate rows will be eliminated, leaving the final result set with only distinct rows.

Read about similar article - Unions Clause.

UNION ALL

Refer to Tables 1 and 2 from the above example of UNION. Here, we will apply UNION ALL to the data of Table 1 and Table 2:

  • SQL

SQL

SELECT name, dept FROM table1

UNION ALL

SELECT name, dept FROM table2;

Output

Union All operation

Explanation

In the illustration mentioned above, Table 1 and Table 2's results are combined using UNION ALL. We choose the columns  "empID", "name", and "dept”  from both tables, then using UNION ALL; we combine the information. Contrary to UNION, duplicate rows won't be eliminated from the result set.

When Should You Use UNION and UNION ALL?

It's crucial to consider your SQL query's specific requirements when selecting whether to use UNION or UNION ALL. 

  1. The UNION operator should be used to integrate datasets and eliminate any duplicate rows. This is helpful when working with data that might have values that are similar or when you wish to combine data from various sources into a single, cohesive dataset.
     
  2. On the other hand, UNION ALL is the operator to employ if you need to join datasets while keeping all rows, including duplicates. For merging datasets with overlapping values or when you want to keep all the rows from each dataset but combine them into a single result set.
     
  3. As the database must carry out additional operations to delete duplicate rows, UNION can be computationally more expensive than UNION ALL. Hence, UNION ALL can be used to provide faster query results if you don't need deduplication. 
     
  4. The decision between UNION and UNION ALL ultimately comes down to the particular requirements of your query and the peculiarities of your data.

Frequently Asked Questions

Which is faster, UNION or UNION ALL?

The UNION ALL operation is generally faster than the UNION operation. UNION combines and removes duplicates from the result set, which involves additional processing. UNION ALL, on the other hand, simply combines the result sets without removing duplicates, resulting in a faster operation.

Does UNION ALL remove duplicates?

No, the UNION ALL operation does not remove duplicates. It combines the result sets from multiple queries without any duplicate elimination. If you want to combine result sets while removing duplicates, you should use the UNION operation.

What are the two main differences between structure and union?

The two main differences between structures and unions are memory allocation and member access. In a structure, each member has its own memory space, leading to a larger overall memory consumption. However, a union shares memory space among its members, so it only requires as much memory as its largest member. Each member in a structure can be accessed independently using its name. In a union, only one member can hold a value at a time, and accessing one member might overwrite the value of another.

Do UNION and UNION ALL operate differently with NULL values?

Yes. UNION treats NULL values as equal, so it will remove duplicate rows even if they differ only in the value of a NULL column. UNION ALL, however, includes all rows, including duplicates, regardless of whether they contain NULL values.

Conclusion

The two fundamental SQL operators, UNION and UNION ALL, enable you to combine the results of two or more SELECT statements. You can choose the best operator for your particular use case by being aware of the distinctions between these two operators. While UNION eliminates duplicate rows, UNION ALL contains every row, including duplicates.

So it's time for you now to refer to other articles based on a similar topic, i.e., UNION and UNION ALL clauses:

We hope this blog has helped you enhance your knowledge of the difference between UNION and UNION ALL. Do visit here to study database management systems in depth and clarify all your concepts. 

Happy learning!

Previous article
Difference between File System and DBMS
Next article
Difference between GROUP BY and ORDER BY
Live masterclass