Difference between UNION and UNION ALL in SQL

Hello guys, if you are wondering how to combine data from multiple tables into one result set in SQL then there are multiple options. Earlier, we have looked at JOIN which allows you to fetch data from multiple table in single query, other option is UNION and UNION ALL which you can use to combine the data from multiple tables. We saved data in SQL tables in the relational database. We may need to choose data from various tables and aggregate the results of all Select statements on occasion. The SQL Union and versus Union All operators in SQL Server are explained in this article. We'll also look at the differences between these two operators, as well as some applications.

But first of all, before going into the difference between each, let's understand what union and union all mean.

UNION in SQL

To aggregate the results of two or more SELECT operations, use the UNION operator.

• Within UNION, every SELECT query must have the same amount of columns.
• The data types in the columns must also be comparable.
• Every SELECT statement's columns must be in the same order.

SYNTAX

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL

To merge the result sets of two or more SELECT queries, use the SQL UNION ALL operator. Between the multiple SELECT queries, it does not eliminate duplicate rows (all rows are returned).

Within the UNION ALL, each SELECT query must have the same amount of fields in the result sets with identical data types.



SYNTAX

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Difference between UNION and UNION ALL

The most significant distinction between UNION and UNION ALL is that:

In UNION, only retains records that are unique. On the other hand, all records, including duplicates, are kept by UNION ALL.

UNION ALL saves all of the records from each of the original data sets, whereas UNION deletes any duplicates. Before providing the merged data set, UNION runs a sorting operation and removes any entries that are duplicated across all columns.

EXAMPLE

Let's understand this by an example of our own. 

Create two tables to perform he operations on. I have created table cricketer and franchise and inserted few data. feel free to create any table and insert any data.


CREATE TABLE cricketer(name varchar(10), age INT(10));
CREATE TABLE franchise(name varchar(10), rank INT(10));

INSERT INTO cricketer(name, age) VALUES('virat', 34), ('rohit', 36);
INSERT INTO franchise(name, rank) VALUES('RCB', 1), ('MI', 2), ('RCB', 1);




UNION on the data


SELECT * FROM cricketer 
UNION
SELECT * FROM franchise;

RESULT

(Note : the columns will appear with headers of first select statement)

UNION ALL on the data

SELECT * FROM cricketer 
UNION ALL
SELECT * FROM franchise;

Result

Also note the below points
The data being merged must meet the same fundamental conditions whether it's a UNION or a UNION ALL:

• Each SELECT query must return the same amount of columns in order to be merged.
• In each SELECT statement, the columns obtained must be in the same sequence.
• The data types of the columns obtained must be identical.

That's all about difference between UNION and UNION ALL in SQL. We've seen how UNION and UNION ALL may be beneficial for concatenating data sets and determining whether or not duplicates should be kept. Before providing the final results, UNION conducts a deduplication step; UNION ALL keeps all duplicates and delivers the entire, concatenated results. To be successful, each SELECT must have the same amount of columns, data types, and data order.

 Other SQL Articles and Tutorials you may like
How to use Cross Join in SQL
How to use ORDER BY in SQL with example
How to use row_number function in SQL
15 SQL Interview Questions with Answers
Difference between DDL and DML commands in SQL
How to remove duplicate rows in SQL
How to concatenate columns in SQL
How to find Nth Highest salary in SQL
How to use LEFT, RIGHT, and SELF JOIN
What is a Virtual column in MySQL? Example
How to do pagination in SQL with Examples
How to transpose data in SQL with examples

Thanks
 for reading this article so far. If you like these difference between UNION and UNION ALL in SQL  then please share it with
 your friends and colleagues. If you have any questions or feedback then
 please drop a note.