用LEFTJOIN代替NOTIN或NOTEXISTS.docVIP

用LEFT JOIN 代替NOT IN?或 NOT EXISTS： SQL conn scott/tigerConnected.SQL CREATE TABLE testa? 2? (? 3?? id number,? 4?? value varchar2(10)? 5? ); Table created. SQL INSERT INTO testa VALUES(1,a); 1 row created. SQL INSERT INTO testa VALUES(2,b); 1 row created. SQL INSERT INTO testa VALUES(3,c); 1 row created. SQL INSERT INTO testa VALUES(4,d); 1 row created. SQL INSERT INTO testa VALUES(5,e); 1 row created. SQL INSERT INTO testa VALUES(6,f); 1 row created. SQL COMMIT; Commit complete.SQL CREATE TABLE testb AS? 2? SELECT * FROM testa WHERE 1=0; Table created. SQL INSERT INTO testb VALUES(2,b); 1 row created. SQL INSERT INTO testb VALUES(4,d); 1 row created. SQL INSERT INTO testb VALUES(6,f); 1 row created. SQL COMMIT; Commit complete. 用左连接，结果testb表里TempColum的值为NULL： SQL SELECT a.*,b.id TempColum FROM testa a LEFT JOIN testb b ON a.id=b.id; ??????? ID?? VALUE?????? TempColum---------- ----------?????? ----------???????? 2?????? b?????????????????? 2???????? 4???? ? d?????????????????? 4???????? 6???? ? f??????????????????? 6???????? 5????? ?e???????????????????NULL????????? 3?????? c?????????????????? NULL????????? 1?????? a???????????????????NULL? 6 rows selected. 将NULL值过滤出来就是最后需要的结果： SQL SELECT c.id,c.value FROM(?SELECT a.*,b.id TempColum FROM testa a LEFT JOIN testb b ON a.id=b.id) cWHERE c.TempColum IS NULLORDER BY c.id SQL??????? ID VALUE---------- ----------???????? 1 a???????? 3 c???????? 5 e