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