The difference between Union and union All in Oracle (for multiple databases) _oracle

Source: Internet
Author: User
Tags dname
The Union and the Union all work together to combine SELECT query result sets, so what's the difference?
The Union merges the resulting set of queries and then checks them to remove the same rows. Disadvantage: low efficiency;
The union all is only the result set of the merged query, does not requery, the efficiency is high, but may appear the redundant data.

Let us give an example to illustrate:

For example, there are two tables Tab1 and TAB2 in the database.

The data in the TAB1 are:

Xiao Wang
Xiao Zhang

The data in the TAB2 are:

Xiao Wang
Xiao Li

To execute a query:

Copy Code code as follows:
SELECT * FROM Tab1 UNION SELECT * TAB2

The results are as follows:
Xiao Wang
Xiao Zhang
Xiao Li
If you execute the following query:

Copy Code code as follows:

SELECT * FROM Tab1 UNION all select * from TAB2

The results are as follows:
Xiao Wang
Xiao Zhang
Xiao Wang
Xiao Li
Is this going to look different now?

The difference between Union and union all is that union automatically compresses repeated results in multiple result sets, and union ALL displays all the results, whether or not they are duplicates.

Union: The two result sets are combined to set operations, excluding duplicate rows, while the default rules are sorted;

Union all: A set of two result sets, including duplicate rows, is not sorted;

Intersect: Intersection of two result sets, excluding duplicate rows, and ordering of default rules;

Minus: Perform a bad operation on two result sets, excluding duplicate rows, and sorting the default rules.

You can specify an ORDER BY clause in the last result set to change the sort method.

For example:
Copy Code code as follows:

Select employee_id,job_id from Employees
Union
Select employee_id,job_id from Job_history

The results of the two tables are combined together. These two examples compress the duplicates in the results of the two SELECT statements, that is, the data of the result is not the same as the number of bars of two results. If you want to use union all even if the results are repeated, for example:

2. There is a table emp in the Scott user of Oracle
Copy Code code as follows:

SELECT * from emp where deptno >= 20
UNION ALL
SELECT * from emp where Deptno <= 30

The results here have a lot of duplicate values.

The issues to note about the Union and UNION ALL keywords are:

Union and UNION ALL can combine multiple result sets, not just two, and you can string together multiple result sets.
Using union and UNION all must ensure that the results of each select collection have the same number of columns, and that each column is of the same type. However, the column name does not necessarily need to be the same, and Oracle takes the column name of the first result as the column name for the result set. For example, here is an example:
Copy Code code as follows:

Select Empno,ename from emp
Union
Select Deptno,dname from Dept

We don't need to sort by using an ORDER BY clause in each select result set, and we can sort the entire result by using an order by. For example:
Copy Code code as follows:

Select Empno,ename from EMP
Union
Select Deptno,dname from Dept
Order by ename;
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.