用leftjoin代替notin或notexists

《用leftjoin代替notin或notexists》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、用LEFTJOIN代替NOTIN 或NOTEXISTS：SQL>connscott/tigerConnected.SQL>CREATETABLEtesta 2 ( 3  idnumber, 4  valuevarchar2(10) 5 );Tablecreated.SQL>INSERTINTOtestaVALUES(1,'a');1rowcreated.SQL>INSERTINTOtestaVALUES(2,'b');1rowcreated.SQL>INSERTINTOtestaVALUES(3,'c');1rowcreated.SQL>INSERTINTOtestaVALUES(4,

2、'd');1rowcreated.SQL>INSERTINTOtestaVALUES(5,'e');1rowcreated.SQL>INSERTINTOtestaVALUES(6,'f');1rowcreated.SQL>COMMIT;Commitcomplete.SQL>CREATETABLEtestbAS 2 SELECT*FROMtestaWHERE1=0;Tablecreated.SQL>INSERTINTOtestbVALUES(2,'b');1rowcreated.SQL>INSERTINTOtestbVALUES(4,'d');1rowcreated.SQL>INSERT

3、INTOtestbVALUES(6,'f');1rowcreated.SQL>COMMIT;Commitcomplete.用左连接，结果testb表里TempColum的值为NULL：SQL>SELECTa.*,b.id"TempColum"FROMtestaaLEFTJOINtestbbONa.id=b.id;       ID  VALUE      TempColum--------------------      ----------        2      b                  2        4     d                  4   

4、     6     f                   6        5      e                   NULL         3      c                  NULL         1      a                   NULL 6rowsselected.将NULL值过滤出来就是最后需要的结果：SQL>SELECTc.id,c.valueFROM( SELECTa.*,b.id"TempColum"FROMtestaaLEFTJOINtestbbONa.id=b.id)cWHEREc."TempColum"ISN

5、ULLORDERBYc.idSQL>       IDVALUE--------------------        1a        3c        5e