表一 有列 t1  t2  t3  t4  t5  t6  t7  t8 其中t1  t2  t3是主键

表二 有列 t1  t2  t3  x1 x2  x3  x4  x5 其中t1  t2  t3是主键

 

如何将两个表中主键值不同的所有数据整合到另外的表或视图中？

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

--创建示例表及示例数据

SQL> create table t1 (t1 varchar(1),t2 varchar(5),t3 varchar(10));

 

表已创建。

 

SQL> insert into t1 values ('1','a','1a');

 

已创建 1 行。

 

SQL> insert into t1 values ('2','b','2b');

 

已创建 1 行。

 

SQL> insert into t1 values ('3','c','3c');

 

已创建 1 行。

 

 

SQL> create table t2 as select * from t1;

 

表已创建。

 

SQL> insert into t1 values ('9','x','9x');

 

已创建 1 行。

 

SQL> insert into t2 values ('8','Y','8Y');

 

已创建 1 行。

 

--查看示例表

SQL> select * from t1;

 

T T2    T3

- ----- ----------

1 a     1a

2 b     2b

3 c     3c

9 x     9x

 

SQL> select * from t2;

 

T T2    T3

- ----- ----------

1 a     1a

2 b     2b

3 c     3c

8 Y     8Y

 

--两表中各有一行与另表中不匹配的记录。

SQL> select * from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3

(+);

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

1 a     1a         1 a     1a

2 b     2b         2 b     2b

3 c     3c         3 c     3c

9 x     9x

 

SQL> select * from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2

.t3;

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

1 a     1a         1 a     1a

2 b     2b         2 b     2b

3 c     3c         3 c     3c

                   8 Y     8Y

 

SQL> select * from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3

(+) and t2.t1 is null;

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

9 x     9x

 

SQL> select * from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2

.t3 and t1.t1 is null;

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

                   8 Y     8Y

 

--生成不匹配表，并插入t1表中与t2表中不匹配的数据

SQL> create table t3 as select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2

.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null;

 

表已创建。

 

SQL> select * from t3;

 

T T2    T3

- ----- ----------

9 x     9x

 

--再插入t2表中与t1表中不匹配的记录

SQL> insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2

.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);

 

已创建 1 行。

 

--查看表3，OK。

SQL> select * from t3;

 

T T2    T3

- ----- ----------

9 x     9x

8 Y     8Y

 

你也可以先创建t3表，然后再两次插入两表中不匹配的记录，如下：

 

SQL> drop table t3;

 

表已丢弃。

 

SQL> create table t3 as select * from t1 where t1.t1='qqq';

 

表已创建。

 

--分别插入两中表中不匹配的数据

--insert into t3 (select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2(+) and t1.t3=t2.t3(+) and t2.t1 is null);

--insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);

 

SQL> insert into t3 (select t1.* from t1,t2 where t1.t1=t2.t1(+) and t1.t2=t2.t2

(+) and t1.t3=t2.t3(+) and t2.t1 is null);

 

已创建 1 行。

 

SQL> insert into t3 (select t2.* from t1,t2 where t1.t1(+)=t2.t1 and t1.t2(+)=t2

.t2 and t1.t3(+)=t2.t3 and t1.t1 is null);

 

已创建 1 行。

 

SQL> select * from t3;

 

T T2    T3

- ----- ----------

9 x     9x

8 Y     8Y

 

SQL>

 

--OK

SELECT 

  t1.t1 AS t1_t1, 

  t1.t2 AS t1_t2, 

  t1.t3 AS t1_t3,

  t2.t1 AS t2_t1, 

  t2.t2 AS t2_t2, 

  t2.t3 AS t2_t3 

FROM  

  t1 FULL JOIN t2 ON 

    ( t1.t1 = t2.t1

      AND t1.t2 = t2.t2

      AND t1.t3 = t2.t3

    )

WHERE

  t1.t1 IS NULL

  OR t2.t1 IS NULL

SQL> SELECT

  2    t1.t1 AS t1_t1,

  3    t1.t2 AS t1_t2,

  4    t1.t3 AS t1_t3,

  5    t2.t1 AS t2_t1,

  6    t2.t2 AS t2_t2,

  7    t2.t3 AS t2_t3

  8  FROM

  9    t1 FULL JOIN t2 ON

 10      ( t1.t1 = t2.t1

 11        AND t1.t2 = t2.t2

 12        AND t1.t3 = t2.t3

 13      )

 14  WHERE

 15    t1.t1 IS NULL

 16    OR t2.t1 IS NULL;

 

T T1_T2 T1_T3      T T2_T2 T2_T3

- ----- ---------- - ----- ----------

9 x     9x

                   8 Y     8Y

 

SQL>

SELECT *

FROM t1 full join t2 on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)

where t1.t1 is null or t2.t1 is null;

select * from t1,t2 

where t1.t1(+)=t2.t1(+) and t1.t2(+)=t2.t2(+) and t1.t3(+)=t2.t3(+)

SQL> SELECT *

  2  FROM t1 full join t2

  3    on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3);

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

1 a     1a         1 a     1a

2 b     2b         2 b     2b

3 c     3c         3 c     3c

9 x     9x

                   8 Y     8Y

 

SQL> SELECT *

  2  FROM t1 full join t2

  3    on (t1.t1=t2.t1 and t1.t2=t2.t2 and t1.t3=t2.t3)

  4  where t1.t1 is null

  5     or t2.t1 is null;

 

T T2    T3         T T2    T3

- ----- ---------- - ----- ----------

9 x     9x

                   8 Y     8Y

 

SQL>