Difference Between UNION and UNION ALL

4 mins read40.2K Views Comment

Assistant Manager - Content

Updated on Jun 23, 2023 11:03 IST

UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both the table, while UNION ALL returns all the records from both the tables. In this article, we will explore the difference between UNION and UNION ALL.

UNION and UNION ALL in SQL are set operators that combine the result 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

UNION vs. UNION ALL

Parameter	UNION	UNION ALL
Definition	Combines the result from the multiple table and return the distinct records into a single result set.	Combines the result from the multiple table and return all the record into a single result set.
Removal of dulicates	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 compare 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 UNION?

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 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 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).

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

Rules

Both the SELECT statement must have the same number of columns and the same data type.
Columns in both the SELECT statement 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 select query.

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

Must Read: SQL LIMITS

Example

Let us have two tables of employee names and manager names. Combine both the table 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 removes 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 the table, while UNION ALL returns all the records from both the 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

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

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 concept is very importamt concept in SQL. This article explains creation,addition and deletion of foreign key.

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

This article includes types of cursors and you will also learn about implementation of cursors using PL/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 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?

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

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.

Download this article as PDF to read offline

Download as PDF

About the Author

Vikram Singh