Difference between Union and Union All in Oracle (applicable to multiple databases)

Source: Internet
Author: User
Tags dname

Union and Union ALL merge query result sets of SELECT. What are their differences?
Union re-queries the query result set and removes the same rows. Disadvantages: Low Efficiency;
Union ALL only merges the query result set and does not re-query. It is highly efficient, but redundant data may occur.

Here is an example:

For example, the database has two tables tab1 and tab2.

Data in tab1 includes:

John
Xiao Zhang

Data in tab2 includes:

John
Xiao Li

Run the query:

Copy codeThe Code is as follows: SELECT * FROM tab1 union select * FROM tab2
The result is as follows:
John
Xiao Zhang
Xiao Li
If you perform the following query:

Copy codeThe Code is as follows:
SELECT * FROM tab1 union all select * FROM tab2

The result is as follows:
John
Xiao Zhang
John
Xiao Li
What's the difference this time?

The difference between union and union all is that union will automatically compress the repeated results in multiple result sets, while union all will display all the results, whether they are repeated or not.

Union: Perform Union operations on two result sets, excluding duplicate rows and sorting by default rules;

Union All: Perform Union operations on two result sets, including duplicate rows without sorting;

Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;

Minus: performs the Difference Operation on two result sets, excluding duplicate rows, and sorts the default rules at the same time.

You can specify the Order by clause in the last result set to change the sorting method.

For example:
Copy codeThe Code is as follows:
Select employee_id, job_id from employees
Union
Select employee_id, job_id from job_history

The above two tables are joined together. The two examples compress the duplicate values in the results of the two select statements, that is, the result data is not the sum of the two results. If you want to use union all even if repeated results are displayed, for example:

2. The scott user in oracle has the table emp.
Copy codeThe Code is as follows:
Select * from emp where deptno> = 20
Union all
Select * from emp where deptno <= 30

The results here have a lot of repeated values.

Note the following when using the union and union all keywords:

Union and union all can combine multiple result sets, not just two. You can concatenate multiple result sets.
When using union and union all, you must ensure that the results of each select set have the same number of columns, and each column has the same type. But the column names do not need to be the same. oracle uses the column name of the first result as the column name of the result set. For example, the following is an example:
Copy codeThe Code is as follows:
Select empno, ename from emp
Union
Select deptno, dname from dept

We do not need to use the order by clause in each select result set for sorting. We can use an order by clause to sort the entire result at the end. For example:
Copy codeThe Code is 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.