oracle join 取并集,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