Union and UNION all differences and performance analysis in Oracle

Summary

Students who often write SQL may use both the Union and UNION all keywords, and perhaps you know that using them can combine the result set of two queries.

So what's the difference between them? Here's a simple analysis.

Comparison

Union: Set operation on two result sets, excluding duplicate rows, equivalent to distinct, and sorting the default rules;

UNION ALL: Set up the two result sets, including duplicate rows, that is, all results are displayed, whether duplicates or not;

Let's take a simple example to prove the above conclusion:

1. Prepare the data:

drop table Student;create table student (     ID int primary KEY,     name NVARCHAR2 () not NULL, score number not     nul l); INSERT into student values (1, ' Aaron ', + +), insert into student values (2, ' Bill ', +), insert into student values (3, ' Cindy INSERT into student values (4, ' Damon ', "n"), insert into student values (5, ' Ella ', "the"), insert into student values (6, ' Frado ', "," INSERT into student values (7, ' Gill ', "), insert into student values (8, ' Hellen '," + "), insert into student values (9, ' Ivan ', (); INSERT into student values ("Jay", "n"); commit;

2. Compare different points

Query comparison ①

--UNION ALL SELECT * FROM student where ID < 4union allselect * from student where ID > 2 and ID < 6   --Unio Nselect * FROM student where ID < 4unionselect * from student where ID > 2 and ID < 6

UNION ALL query results:

Union query Result:

By comparison It is not difficult to see that union all does not remove duplicate records, and The union removes the duplicate records.

Query comparison ②

--Union Allselect * from student where ID > 2 and ID < 6union allselect * FROM student where ID < 4--Unionselec T * from student where ID > 2 and ID < 6unionselect * from student where ID < 4

UNION ALL query results:

Union query Result:

It is not difficult to see that union all organizes data in the order in which it is associated, and the Union sorts according to certain rules.

So what is the rule? We use the following query to draw a pattern:

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

Conclusion: Sort by the order in which the fields appear, the previous query is equivalent to the order by ID, name, score, just the query equivalent to order by score, ID, name.

Summary

1. Because union all is simply a simple merge query result, it does not do the redo or sort, so the union all is more efficient than the Union.

So if you can determine that there are no duplicate records, use union all as much as possible.

2. Typically, if a table has multiple indexed columns, using union to replace or in the WHERE clause will have a good effect, and the index column using or will cause a full table scan.

Note: The above rules are valid only for multiple indexed columns, or if there is a column that is not indexed.

For example, using the example above, assume 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 Betwee N and 90--inefficient select ID, name, score from student where name like '%y% ' or score between and 90