Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
SQL> create table enmo(id number,name varchar2(20));
Table created.
SQL> create table yhem(id number,name varchar2(20));
Table created.
SQL> insert into enmo values(1,'EYGLE');
1 row created.
SQL> insert into yhem values(1,'EYGLE');
1 row created.
SQL> insert into enmo values(2,'KAMUS');
1 row created.
SQL> insert into yhem values(2,'KAMUS');
1 row created.
SQL> insert into yhem values(3,'YANGTINGKUN');
1 row created.
SQL> insert into yhem values(4,'ORA-600');
1 row created.
EXCEPT 和 MINUS 等价比较：
SQL> select * from yhem
2 minus
3 select * from enmo;
ID NAME
---------- --------------------
3 YANGTINGKUN
4 ORA-600
SQL> select * from yhem
2 except
3 select * from enmo;
ID NAME
---------- --------------------
3 YANGTINGKUN
4 ORA-600
SQL> select * from enmo
2 minus
3 select * from yhem;
no rows selected
SQL> insert into enmo values(5,'LAOXIONG');
1 row created.
SQL> select * from enmo
2 minus
3 select * from yhem;
ID NAME
---------- --------------------
5 LAOXIONG
SQL> select * from enmo
2 except
3 select * from yhem;
ID NAME
---------- --------------------
5 LAOXIONG
EXCEPT 通过 ALL 关键字显示所有差异记录，不去重复值：
SQL> insert into yhem values(4,'ORA-600');
1 row created.
SQL> select * from yhem
2 except
3 select * from enmo;
ID NAME
---------- --------------------
3 YANGTINGKUN
4 ORA-600
SQL> select * from yhem
2 except all
3 select * from enmo;
ID NAME
---------- --------------------
3 YANGTINGKUN
4 ORA-600
4 ORA-600
