Explanation of Union and union All in SQL

SQL Union and UNION ALL operators

The UNION operator is used to combine the result set of two or more SELECT statements.

Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

Suppose we have a table student that includes the following fields and data:

drop table student;

CREATE TABLE Student
(
ID int PRIMARY KEY,
Name NVARCHAR2 () NOT NULL,
Score number NOT NULL
);

INSERT into student values (1, ' Aaron ', 78);
INSERT into student values (2, ' Bill ', 76); INSERT into student values (3, ' Cindy ', 89);
INSERT into student values (4, ' Damon ', 90);
INSERT into student values (5, ' Ella ', 73);
INSERT into student values (6, ' Frado ', 61);
INSERT into student values (7, ' Gill ', 99); INSERT into student values (8, ' Hellen ', 56);
INSERT into student values (9, ' Ivan ', 93);
INSERT into student values (, ' Jay ', 90);

Commit

The difference between Union and union all.

SELECT *
From student
Where ID < 4

Union

SELECT *
From student
where ID > 2 and ID < 6

The result will be

1 Aaron 78
2 Bill 76
3 Cindy 89
4 Damon 90
5 Ella 73

If you switch to union ALL to connect two result sets, the result is:

1 Aaron 78
2 Bill 76
3 Cindy 89
3 Cindy 89
4 Damon 90
5 Ella 73

As you can see, one of the differences between Union and union all is the processing of duplicate results.

The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. Most of the actual applications do not produce duplicate records, the most common being the process table and the History table Union. Such as:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large.
The union all simply merges two results and returns. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data.
In terms of efficiency, union All is much faster than union, so if you can confirm that the combined two result sets do not contain duplicate data, then use UNION ALL,

Explanation of Union and union All in SQL