Difference Between UNION and UNION ALL

Difference Between UNION and UNION ALL

5 mins read40.3K Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jun 10, 2024 11:31 IST

UNION and UNION ALL are set operators in SQL used to concatenate the results of two or more SELECT statements. Still, they differ from each other in how they handle duplicate records. The UNION command combines two or more SELECT statements but removes duplicates from the final result set, whereas the UNION ALL statement does not remove the duplicate from the result. UNION ALL simply concatenates all records, including duplicates from the SELECT statement. This article will explore the difference between UNION and UNION ALL.

2022_09_Feature-Image-Templates-42.jpg

UNION and UNION ALL in SQL are set operators that combine the results of two SELECT queries. Both share standard features, with one significant difference: UNION only returns a unique record, while UNION ALL returns all the records (including duplicates). This article will explore the difference between UNION and UNION ALL.

Must Check: Free Database and SQL Online Course and Certificates 

Must Check: SQL tutorial

Table of Content

UNION vs. UNION ALL

Parameter UNION UNION ALL
Definition Combines the result from the multiple tables and returns the distinct records into a single result set. Combines the result from the multiple tables and returns all the records into a single result set.
Removal of duplicates It has a default feature to eliminate duplicate rows from the table. It can’t eliminate the duplicate rows from the table.
Performance Slow performance as it removes duplicate rows from the output. Fast performance as compared to UNION.
Syntax SELECT column_names
FROM table_1
WHERE conditions
UNION
SELECT column_names
FROM table_2
WHERE conditions
SELECT column_names
FROM table_1
WHERE conditions
UNION ALL
SELECT column_names
FROM table_2
WHERE conditions

Read Also: Update Query in SQL

What is a UNION?

UNION in SQL combines data from two SELECT queries' results into a single distinct result set. The result set to produce from UNION doesn’t contain any duplicate value.

Syntax


 
SELECT column_names
FROM table_1
WHERE conditions
UNION
SELECT column_names
FROM table_2
WHERE conditions;
Copy code

Must Read: SQL Logical Operator

What is UNION ALL?

UNION ALL is an extension of UNION in SQL. As the result set of UNION of two tables doesn’t contain all the records from both tables, UNION ALL comes into the picture and returns all the records from both tables (including duplicate rows also).

Syntax


 
SELECT column_names
FROM table_1
WHERE conditions
UNION ALL
SELECT column_names
FROM table_2
WHERE conditions;
Copy code

Also Read: How to use UNION in SQL

Enrol now in leading online courses from top colleges to explore the world of SQL and databases. Advance your career in database management.

Rules

  • The SELECT statements must have the same number of columns and data types.
  • Columns in both the SELECT statements must be in the same order.
  • The names of the columns can be different, but the final result set will show the column name of the first selected query.

Let’s understand the difference between UNION and UNION ALL by an example.

Must Read: SQL LIMITS

Discover the pinnacle of job-centric courses after 12th. Embark on a journey to a rewarding career with specialized online degree programs.

Example

Let us have two tables of employee names and manager names. Combine both the tables using UNION and UNION ALL.

Employee

Employee_id Name
1 Ram
2 Shyam
3 Joya
4 Vidit

Manager

Employee_id Name
1 Ram
4 Vidit
 

UNION


 
SELECT Employee_id, name
FROM Employee
UNION
SELECT *
FROM Manager;
Copy code

output

Employee_id Name
1 Ram
2 Shyam
3 Joya
4 Vidit

UNION ALL


 
SELECT Employee_id, name
FROM Employee
UNION
SELECT *
FROM Manager;
Copy code

output

Employee_id Name
1 Ram
2 Shyam
3 Joya
4 Vidit
1 Ram
4 Vidit

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties

Key differences

  • Both UNION and UNION ALL combine the result of two or more tables.
  •  The result set of UNION does not contain duplicate rows, while the result set of UNION ALL returns all the rows from both tables.
  • The execution time of UNION ALL is less than the execution time of UNION as it does not remove the duplicate rows.

Conclusion

UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both tables, while UNION ALL returns all the records from both tables.

In the article, we have briefly discussed UNION and UNION ALL, the differences between them, and examples.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!

Related Reads:

How to use Rank and Dense Rank in SQL
How to use Rank and Dense Rank in SQL
Rank and Dense Rank Functions are the window function used to order values and assign them some number depending on whether they fall in relation to one another. In this article, we...read more
Cross Join in SQL
Cross Join in SQL
SQL Cross Join combines each row of one table with each row of another table and the number of rows in the result set is the product of number of...read more
FOREIGN KEY IN SQL
FOREIGN KEY IN SQL
Foreign key concept is very important concept in SQL. This article explains creation, addition and deletion of foreign key. This article explains foreign key in SQL. It also explains the...read more
A Beginner’s Guide to SQL String Functions 
A Beginner’s Guide to SQL String Functions 
In this article we will explore different functions like LENGTH,CONCAT,SUBSTRING,TRIM Function LTRIM and RTRIMUPPER and LOWER, REPLACE, LPAD and RPAD functions with examples.
Implementing Cursors with PL/SQL
Implementing Cursors with PL/SQL
This article includes types of cursors and you will also learn about implementation of cursors using PL/SQL.
What are TCL Commands in SQL?
What are TCL Commands in SQL?
SQL, or Structured Query Language, is a database language that allows you to create a database and perform various operations. This is done using various types of SQL commands, such...read more
Outlier Detection Using SQL
Outlier Detection Using SQL
Outlier detection is an important step in data analysis and can be used to identify errors, gain insights, improve predictive models, and detect fraud.
What are DCL Commands in SQL?
What are DCL Commands in SQL?
SQL, or Structured Query Language, is a database language that allows you to create a database and perform various operations. This is done using various types of SQL commands, such...read more
Difference Between WHERE and HAVING Clause
Difference Between WHERE and HAVING Clause
Are you aware of the difference between WHERE and HAVING clause in SQL? Are you aware of when to use the WHERE clause and when to use the HAVING clause?...read more

FAQs

What is UNION in SQL?

UNION in SQL combines data from the result set of two SELECT queries into a single distinct result set. The result set to produce from UNION does not contain any duplicate value.

What is UNION ALL in SQL?

UNION ALL is an extension of UNION in SQL. As the result set of UNION of two tables doesnu2019t contains all the records from both the table, so UNION ALL comes into the picture that returns all the records from both the table (including duplicate rows also).

What is the difference between UNION and UNION ALL?

The result set of UNION does not contain duplicate rows, while the result set of UNION ALL returns all the rows from both tables. The execution time of UNION ALL is less than the execution time of UNION as it does not removes the duplicate rows.

When to use: UNION vs UNION ALL?

Use UNION when you need a result set that must be free of duplicates. It's ideal when the uniqueness of rows is important for your query or analysis​.

Are there any requirements for the SELECT statements used in UNION and UNION ALL?

Yes, the SELECT statements must have the same number of columns in the result set, and the columns must be of compatible types. The columns do not need to have the same names, but they must be in the same order.

How does UNION handle NULL values?

In SQL, NULL values are treated as distinct from each other. Therefore, if a column contains NULL in different rows, UNION will not consider these rows as duplicates and will include them in the result set.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio