CREATE TABLE guotest1 (sName VARCHAR2(10), Gender VARCHAR2(10));
CREATE TABLE guotest2 (sName VARCHAR2(10), Gender VARCHAR2(10));
INSERT INTO guotest1 VALUES ('a','m');
INSERT INTO guotest1 VALUES ('b','m');
INSERT INTO guotest1 VALUES ('c','f');
INSERT INTO guotest1 VALUES ('d','f');
INSERT INTO guotest2 VALUES ('e','m');
INSERT INTO guotest2 VALUES ('f','m');
INSERT INTO guotest2 VALUES ('g','f');
INSERT INTO guotest2 VALUES ('h','f');
SELECT * FROM guotest1 WHERE ROWNUM <= 4 MINUS SELECT * FROM guotest1 WHERE ROWNUM <= 2;
結果:
SNAME GENDER c f d f
SELECT * FROM guotest1 MINUS SELECT * FROM guotest2;
結果:
SNAME GENDER a m b m c f d f
語義:左表中「不存在於右表」的行才會回傳。
MINUS 會自動去重(集合運算),若需保留重複行請用 NOT EXISTS 或 LEFT JOIN ... WHERE right.key IS NULL。