讲稿其他数据库对象

子查询

EpisodeIX,PartII子查询EpisodeIX结构目标预备知识

回顾内容

总结

作业目标定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询预备知识回顾内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询问题谁的工资比Abel的高谁的工资比Abel的高?主查询:Abel工资是多少?子查询:子查询语法子查询在主查询之前执行主查询要使用子查询的结果SELECT select_listFROM tableWHERE exproperator (SELECT select_list FROM table);SELECTlast_name,salaryFROMemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHERElast_name='Abel');11000子查询子查询必须放在括号中子查询最好放在运算符的右侧增强可读性单行子查询使用单行运算符，多行子查询使用多行运算符内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询子查询分类单行子查询多行子查询MainquerySubquery

returnsST_CLERKST_CLERKSA_MANMainquerySubquery

returns内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询单行子查询仅仅返回一行必须使用单行比较运算符Greaterthanorequalto>=Lessthan<Lessthanorequalto<=Equalto=Notequalto<>Greaterthan>MeaningOperatorSELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHERElast_name=‘Taylor’)ANDsalary>(SELECTsalaryFROMemployeesWHERElast_name=‘Taylor’);SA_REP8600SELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);2500SELECTdepartment_id,MIN(salary)FROMemployeesGROUPBYdepartment_idHAVINGMIN(salary)>(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=50);2500…多行子查询子查询返回多行必须使用多行比较运算符Mustbeprecededby=,!=,>,<,<=,>=.Comparesavaluetoeveryvalueinalistorreturnedbyaquery.EvaluatestoTRUEifthequeryreturnsnorows.ALLEqualtoanymemberinthelistINMustbeprecededby=,!=,>,<,<=,>=.Comparesavaluetoeachvalueinalistorreturnedbyaquery.EvaluatestoFALSEifthequeryreturnsnorows.ANYMeaningOperatorANY运算符SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ANY(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';9000,6000,4200…使用ALL运算符SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ALL(SELECTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';9000,6000,4200子查询中的NULL值SELECTemp.last_nameFROMemployeesempWHEREemp.employee_idNOTIN(SELECTmgr.manager_idFROMemployeesmgr);内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery10090

10260

12450多列子查询SELECT employee_id,manager_id,department_idFROM empl_demoWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMempl_demoWHEREfirst_name='John')ANDfirst_name<>'John';SELECTemployee_id,manager_id,department_idFROMempl_demoWHEREmanager_idIN(SELECTmanager_idFROMempl_demoWHEREfirst_name='John')ANDdepartment_idIN(SELECTdepartment_idFROMempl_demoWHEREfirst_name='John')ANDfirst_name<>'John';内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询标量子查询返回一行一列可以在CASE，DECODE中使用可以在除了GROUPBY之外的任何SELECT子句中使用SELECTemployee_id,last_name,(CASEWHENdepartment_id=

(SELECTdepartment_id

FROMdepartmentsWHERElocation_id=1800)THEN'Canada'ELSE'USA'END)locationFROMemployees;SELECTemployee_id,last_nameFROMemployeeseORDERBY(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);

20内容定义子查询描述子查询分类书写单行和多行子查询书写多列子查询书写标量子查询书写相关子查询相关子查询选取从外部查询中获取一候选行执行使用候选行执行内部查询使用内部查询的结果用来限定外部查询的行子查询中需要使用父查询表中的列SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator (SELECTcolumn1,column2FROMtable2WHEREexpr1= .expr2);outerouterSELECTlast_name,salary,department_idFROMemployeesouterWHEREsalary>

(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);SELECTe.employee_id,last_name,e.job_idFROMemployeeseWHERE2<=(SELECTCOUNT(*)FROMjob_historyWHEREemployee_id=e.employee_id);EXIST运算符EXISTS测试行在子查询中的存在性如果子查询中行存在子查询停止查找条件标记为TRUE如果子查询中行不存在条件标记为FALSE子查询中继续查找SELECTemployee_id,last_name,job_id,department_idFROMemployeesouterWHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=