[sql]  
create table test1   
(   
 name varchar(10),   
 NN varchar(10)   
);   
insert into test1 values('test','A');   
insert into test1 values('test1','B');   
insert into test1 values('test1','C');   
insert into test1 values('test1','D');   
insert into test1 values('test1','E');   
create table test2   
(   
 name varchar(10),   
 NN varchar(10)   
);   
insert into test2 values('test','A');   
insert into test2 values('test2','B');   
insert into test2 values('test2','C');   
insert into test2 values('test2','D');   
insert into test2 values('test2','E');   
 
1、交集：intersect 
[sql]  
SQL> select * from test1 intersect select * from test2;   
   
NAME       NN   
---------- ----------   
test       A   
 
2、并集：union、union all （注意两者的区别） 
[sql]  
SQL> select * from test1 union select * from test2;   
   
NAME       NN   
---------- ----------   
test       A   
test1      B   
test1      C   
test1      D   
test1      E   
test2      B   
test2      C   
test2      D   
test2      E   
   
9 rows selected.   
[sql]  
SQL> select * from test1 union all select * from test2;   
   
NAME       NN   
---------- ----------   
test       A   
test1      B   
test1      C   
test1      D   
test1      E   
test       A   
test2      B   
test2      C   
test2      D   
test2      E   
   
10 rows selected.   
 
3、差集：minus 
[sql]  
SQL> select * from test1 minus select * from test2;   
   
NAME       NN   
---------- ----------   
test1      B   
test1      C   
test1      D   
test1      E   
   
SQL> select * from test2 minus select * from test1;   
   
NAME       NN   
---------- ----------   
test2      B   
test2      C   
test2      D   
test2      E   
 
最后对于求交集用intersect效率高呢还是hash join效率高呢? 
[sql]  
SQL> select * from test1 intersect select * from test2;   
   
   
Execution Plan   
----------------------------------------------------------   
Plan hash value: 4290880088   
   
-----------------------------------------------------------------------------   
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |   
-----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |       |     5 |   140 |     8  (63)| 00:00:01 |   
|   1 |  INTERSECTION       |       |       |       |            |          |   
|   2 |   SORT UNIQUE       |       |     5 |    70 |     4  (25)| 00:00:01 |   
|   3 |    TABLE ACCESS FULL| TEST1 |     5 |    70 |     3   (0)| 00:00:01 |   
|   4 |   SORT UNIQUE       |       |     5 |    70 |     4  (25)| 00:00:01 |   
|   5 |    TABLE ACCESS FULL| TEST2 |     5 |    70 |     3   (0)| 00:00:01 |   
-----------------------------------------------------------------------------   
   
Note   
-----   
   - dynamic sampling used for this statement (level=2)   
   
   
Statistics   
----------------------------------------------------------   
          0  recursive calls   
          0  db block gets   
         14  consistent gets   
          0  physical reads   
          0  redo size   
        590  bytes sent via SQL*Net to client   
        523  bytes received via SQL*Net from client   
          2  SQL*Net roundtrips to/from client   
          2  sorts (memory)   
          0  sorts (disk)   
          1  rows processed   
   
   
SQL> select a.* from test1 a,test2 b where a.name=b.name and a.nn=b.nn;   
   
   
Execution Plan   
----------------------------------------------------------   
Plan hash value: 497311279   
   
----------------------------------------------------------------------------   
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |   
----------------------------------------------------------------------------   
|   0 | SELECT STATEMENT   |       |     5 |   140 |     7  (15)| 00:00:01 |   
|*  1 |  HASH JOIN         |       |     5 |   140 |     7  (15)| 00:00:01 |   
|   2 |   TABLE ACCESS FULL| TEST1 |     5 |    70 |     3   (0)| 00:00:01 |   
|   3 |   TABLE ACCESS FULL| TEST2 |     5 |    70 |     3   (0)| 00:00:01 |   
----------------------------------------------------------------------------   
   
Predicate Information (identified by operation id):   
---------------------------------------------------   
   
   1 - access("A"."NAME"="B"."NAME" AND "A"."NN"="B"."NN")   
   
Note   
-----   
   - dynamic sampling used for this statement (level=2)   
   
   
Statistics   
----------------------------------------------------------   
          0  recursive calls   
          0  db block gets   
         15  consistent gets   
          0  physical reads   
          0  redo size   
        590  bytes sent via SQL*Net to client   
        523  bytes received via SQL*Net from client   
          2  SQL*Net roundtrips to/from client   
          0  sorts (memory)   
          0  sorts (disk)   
          1  rows processed