oracle交集,并集,差集

我会带着你远行 2022-08-06 04:24 204阅读 0赞

# oracle交集,并集,差集 #

**分类：**  [数据库][Link 1] **/**  [Oracle][] **/**  [文章][Oracle]

oracle **交集**,并集,差集

[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

[Link 1]: http://www.verydemo.com/one_c56.html
[Oracle]: http://www.verydemo.com/two_c158.html