Difference between union and unionall in oracle and Performance Analysis

[Overview]

Those who frequently write SQL statements may use the union and union all keywords. You may know that they can be used to merge the result sets of the two queries,

What is the difference between the two? The following is a simple analysis.

[Comparison]

Union: Perform union operations on two result sets, excluding duplicate rows. This operation is equivalent to distinct and sorting by default rules at the same time;

Union all: Perform union operations on two result sets, including duplicate rows. That is, all results are displayed, whether or not they are repeated;

Here is a simple example to prove the above conclusion:

1. Prepare data:

drop table student;create table student(     id int primary key,     name nvarchar2(50) 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(10,'Jay',90);commit;

2. Comparison of differences

Query comparison ①

-- union all select * from student where id < 4union allselect * from student where id > 2 and id < 6   -- unionselect * from student where id < 4unionselect * from student where id > 2 and id < 6

Union all query results:

Union query results:

It is not difficult to see that union all does not remove duplicate records, while union removes duplicate records.

Query comparison ②

-- union allselect * from student where id > 2 and id < 6union allselect * from student where id < 4-- unionselect * from student where id > 2 and id < 6unionselect * from student where id < 4

Union all query results:

Union query results:

It is not difficult to see that union all organizes data according to the associated order, while union will sort data according to certain rules.

So what is this rule? We obtain the rule through the following query:

-- unionselect score,id,name from student where id > 2 and id < 6unionselect score,id,name from student where id < 4

Conclusion: sort the fields in the order they appear. The previous query is equivalent to order by id, name, and score. The previous query is equivalent to order by score, id, and name.

[Summary]

1. Because union all is just a simple merge query result, it does not perform deduplication or sorting, so union all is more efficient than union.

Therefore, we recommend that you use union all if no duplicate records exist.

2. If the table has multiple index columns, replacing or in the where clause with union will have better results. Using or in the index column will cause full table scanning.

Note: The above rules are only valid for multiple index columns. If a column is not indexed, use or.

For example, we still use the above example, assuming that the name and score are indexed.

-- Efficient select id, name, score from student where name like '% y %' unionselect id, name, score from student where score between 80 and 90 -- inefficient select id, name, score from student where name like '% y %' or score between 80 and 90