oraclesql课件-第二天上午子查询

本章4使用子查询解决问谁的工资比SCOTT高子查询语子查SELECT*FROMSELECT*FROMEMPWHERESAL>(SELECTFROMWHEREENAME=注意事子查询类单行子查只返回一行使用单行比较操作执行单行子查在子查询中使用组函子查询中HAVING子首先执行子查询向主查询中的HAVING子句返回结果使用子查单行子查询中的空值问多行返回多行使用多行比较操作在多行子查询中使用ANY操作查询工资比30号部门任意一个员工高的员工信SELECTSELECTFROMWHEREsal>any(SELECTFROMWHEREdeptno=查询工资比30SELECTSELECTFROMWHEREsal>all(SELECTFROMWHEREdeptno=多行子查询中的空值问查询不 的员工信SELECTSELECTFROMWHEREempnonotin(SELECTFROMnorows摘自 培训讲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