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(*)，那麼這一點不同就看不出來了。