oracle 两表不同的数据库,Oracle 检测出两个表中的不同数据

SQL> create table t1(id number,name varchar2(10));

Table created.

SQL> create table t2(id number,name varchar2(10));

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> insert into t2 values(1,'wh');

1 row created.

SQL> insert into t2 values(2,'wr');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

ID NAME

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

1 wh

2 wp

3 wj

SQL> select * from t2;

ID NAME

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

1 wh

2 wr

SQL> (

2  select id,name,count(*)

3  from t1

4  group by id,name

5  minus

6  select id,name,count(*)

7  from t2

8  group by id,name

9  )

10  union all

11  (

12  select id,name,count(*)

13  from t2

14  group by id,name

15  minus

16  select id,name,count(*)

17  from t1

18  group by id,name

19  )

20  ;

ID NAME         COUNT(*)

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

2 wp                  1

3 wj                  1

2 wr                  1

在这里你可能会举得select语句中的count(*)列可有可无，那么我们再向t2表中增加一条同样的记录试试看。

SQL> insert into t2 values(1,'wh');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID NAME

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

1 wh

2 wr

1 wh

SQL> (

2  select id,name,count(*)

3  from t1

4  group by id,name

5  minus

6  select id,name,count(*)

7  from t2

8  group by id,name

9  )

10  union all

11  (

12  select id,name,count(*)

13  from t2

14  group by id,name

15  minus

16  select id,name,count(*)

17  from t1

18  group by id,name

19  )

20  ;

ID NAME         COUNT(*)

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

1 wh                  1

2 wp                  1

3 wj                  1

1 wh                  2

2 wr                  1

这个时候就看出来区别了，如果t2中存在两条一样的数据，而t1中只存在相应的一条记录，而你又没有使用count(*)，那么这一点不同就看不出来了。