How to query and merge in oracle

Method: 1. Use union to merge queries to remove duplicate rows from the merged result set; 2. Use "union all" to merge queries without removing duplicate rows from the result set; 3. Use intersect to merge queries and get the query The intersection of the results; 4. Use minus to merge the queries and get the difference set of the query results.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to merge queries in oracle

Sometimes in actual applications, in order to merge the results of multiple select statements, you can use the set operation symbols union, union all, intersect, and minus.

It is mostly used for data bureaus with relatively large amounts of data and runs quickly.

1). union

This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set are automatically removed.

Examples are as follows:

SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

2).union all

This operator is similar to union, but it does not cancel duplicate rows and does not will be sorted.

Examples are as follows:

SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set are not automatically removed.

3). intersect

Use this operator to obtain the intersection of two result sets.

Examples are as follows:

SELECT ename, sal, job FROM emp WHERE sal >2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

4). minus

Use this operator to obtain the difference set of two result sets. It will only Displays data that exists in the first collection but not in the second collection.

Examples are as follows:

SELECT ename, sal, job FROM emp WHERE sal >2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

(MINUS means subtraction)

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query and merge in oracle. For more information, please follow other related articles on the PHP Chinese website!