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表中增加一条同样的记录试试看。
Oracle中默认值default的使用方法
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(*)，那么这一点不同就看不出来了。