Basic Oracle Tutorial: Set Operations

Set Operations
Used to merge results of multiple select statements
 
Union deduplication
Union all union
Intersect Intersection
Minus difference set
 
Union
Merge set A and Set B, but remove the duplicate parts of the Two Sets and sort them.
SCOTT @ ora10g> select deptno, ename from emp where deptno in (20, 30)
2 union
3 select deptno, ename from emp where deptno in (20, 10)
4;
DEPTNO ENAME
--------------------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30.
30 JAMES
30 MARTIN
30 TURNER
30 WARD

14 rows selected.

SCOTT @ ora10g>

Union all
Merge set A and Set B without duplicates or sorting

SCOTT @ ora10g> select deptno, ename from emp where deptno in (20, 30)
2 union all
3 select deptno, ename from emp where deptno in (20, 10)
4 *
SCOTT @ ora10g>/

DEPTNO ENAME
--------------------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30.
20 SCOTT
30 TURNER
20 ADAMS
30 JAMES
20 FORD
20 SMITH
20 JONES
10 CLARK
20 SCOTT
10 KING
20 ADAMS
20 FORD
10 MILLER

19 rows selected.

SCOTT @ ora10g>

Intersect
The intersection of the two sets, sorted and de-duplicated

SCOTT @ ora10g> select deptno, ename from emp where deptno in (20, 30)
2 intersect
3 select deptno, ename from emp where deptno in (20, 10)
4 *
SCOTT @ ora10g>/

DEPTNO ENAME
--------------------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH

SCOTT @ ora10g>

Minus
Take the difference set of the two sets, which exists in the set and does not exist in the B set (take the data that does not exist in the B set in the set) de-duplicate
SCOTT @ ora10g> select deptno, ename from emp where deptno in (20, 30)
2 minus
3 select deptno, ename from emp where deptno in (20, 10)
4 *
SCOTT @ ora10g>

DEPTNO ENAME
--------------------
30 ALLEN
30.
30 JAMES
30 MARTIN
30 TURNER
30 WARD

6 rows selected.

SCOTT @ ora10g>