Shulou(Shulou.com)06/01 Report--

The collection operation in Oracle is used to merge the results of multiple select statements. The functions used for collection operations are as follows:

Union Union de-duplicates / / sort operation by default

Union all union does not deduplicate / / does not sort by default

Intersect intersection / / and de-reorder

Minus subtraction / / subtractions in the previous table and not in the latter table

1.union

The merge of the A collection and the B collection, but removing the duplicate parts of the two sets will sort.

Example:

Select deptno,ename from emp where deptno in (20 CLARK10 KING10 MILLER20 ADAMS20 FORD20 JONES20 SCOTT20 SMITH30 ALLEN30 BLAKE30 JAMES30 MARTIN30 TURNER30 WARD14 rows selected 30) unionselect deptno,ename from emp where deptno in (20 charge 10), DEPTNO ENAME--10.

2.union all

The combination of A set and B set, no duplicates, no sorting.

Example:

Select deptno,ename from emp where deptno in (20jue 30) union allselect deptno,ename from emp where deptno in (20jue 10); DEPTNO ENAME--20 SMITH30 ALLEN30 WARD20 JONES30 MARTIN30 BLAKE20 SCOTT30 TURNER20 ADAMS30 JAMES20 FORD20 SMITH20 JONES10 CLARK20 SCOTT10 KING20 ADAMS20 FORD10 MILLER19 rows selected.

3.intersect

The intersection of two sets, sorted and deduplicated.

Example:

Select deptno,ename from emp where deptno in (20jue 30) intersectselect deptno,ename from emp where deptno in (20jue 10); DEPTNO ENAME--20 ADAMS20 FORD20 JONES20 SCOTT20 SMITH5 rows selected

4.minus

Take the difference between the two sets, the data that exists in the A set and does not exist in the B set (take the data that the B set does not exist in the A set) to duplicate.

Example:

Select deptno,ename from emp where deptno in (20 ~ 30) minusselect deptno,ename from emp where deptno in (20 ~ ~ 10), DEPTNO ENAME--30 ALLEN30 BLAKE30 JAMES30 MARTIN30 TURNER30 WARD6 rows selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.