oraclesql課件-第二天上午子查詢

本章4使用子查詢解決問誰的工資比SCOTT高子查詢語子查SELECT*FROMSELECT*FROMEMPWHERESAL>(SELECTFROMWHEREENAME=注意事子查詢類單行子查只返回一行使用單行比較操作執(zhí)行單行子查在子查詢中使用組函子查詢中HAVING子首先執(zhí)行子查詢向主查詢中的HAVING子句返回結(jié)果使用子查單行子查詢中的空值問多行返回多行使用多行比較操作在多行子查詢中使用ANY操作查詢工資比30號(hào)部門任意一個(gè)員工高的員工信SELECTSELECTFROMWHEREsal>any(SELECTFROMWHEREdeptno=查詢工資比30SELECTSELECTFROMWHEREsal>all(SELECTFROMWHEREdeptno=多行子查詢中的空值問查詢不 的員工信SELECTSELECTFROMWHEREempnonotin(SELECTFROMnorows摘自 培訓(xùn)講ReturningNullsintheResultingSetofaTheSQLstatementontheslideattemptstodis yalltheemployeeswhodonothaveanysubordinates.Logically,thisSQLstatementshouldhavereturned12rows.However,theSQLstatementdoesnotreturnanyrows.Oneofthevaluesreturnedbytheinnerqueryisanullvalue,andhencetheentirequeryreturnsnorows.Thereasonisthatallconditionsthatcompareanullvalueresultinanull.Sowhenevernullvaluesarelikelytobepartoftheresultssetofasubquery,donotusetheNOTINoperator.TheNOTINoperatorisequivalentto<>ALL.NoticethatthenullvalueaspartoftheresultssetofasubqueryisnotaproblemifyouusetheINoperatorTheINoperatorisequivalentto=ANY.Forexample,todis ytheemployeeswhohavesubordinates,usethefollowingSQLstatement:SELECT employeesWHEREemp.employee_id(SELECT employeesAlternatively,aWHEREclausecanbeincludedinthesubquerytodis yallemployeeswhodonothaveanysubordinates:SELECTlast_nameFROMWHEREemployee_idNOT(SELECT