Oracle_9i详解之高级子查询

ORACLE_9I详解之高级子查询目标通过本章学习，您将可以:书写多列子查询子查询对空值的处理在FROM子句中使用子查询在SQL中使用单列子查询相关子查询书写相关子查询使用子查询更新和删除数据使用EXISTS和NOTEXISTS操作符使用WITH子句子查询子查询是嵌套在SQL语句中的另一个SELECT语句SELECT...FROM...WHERE...(SELECT...FROM...WHERE...)主查询子查询子查询子查询(内查询)在主查询执行之前执行主查询使用子查询的结果(外查询)SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHEREemployee_id=149);子查询应用举例10500多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery100901026012450主查询与子查询返回的多个列进行比较列比较多列子查询中的比较分为两种:成对比较不成对比较成对比较举例SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(178,174))ANDemployee_idNOTIN(178,174);不成对比较举例SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))ANDemployee_idNOTIN(174,141);SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salaryb.salavg;在FROM子句中使用子查询单列子查询表达式单列子查询表达式是在一行中只返回一列的子查询Oracle8i只在下列情况下可以使用,例如:SELECT语句(FROM和WHERE子句)INSERT语句中的VALUES列表中Oracle9i中单列子查询表达式可在下列情况下使用:DECODE和CASESELECT中除GROUPBY子句以外的所有子句中单列子查询应用举例在CASE表达式中使用单列子查询SELECTemployee_id,last_name,(CASEWHENdepartment_id=THEN'Canada'ELSE'USA'END)locationFROMemployees;(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1800)在ORDERBY子句中使用单列子查询SELECTemployee_id,last_nameFROMemployeeseORDERBY20(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);相关子查询相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询GET从主查询中获取候选列EXECUTE子查询使用主查询的数据USE如果满足内查询的条件则返回该行相关子查询SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator(SELECTcolum1,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);EXISTS操作符EXISTS操作符检查在子查询中是否存在满足条件的行如果在子查询中存在满足条件的行:不在子查询中继续查找条件返回TRUE如果在子查询中不存在满足条件的行:条件返回FALSE继续在子查询中查找SELECTemployee_id,last_name,job_id,department_idFROMemployeesouterWHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);EXISTS操作符应用举例SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT'X'FROMemployeesWHEREdepartment_id=d.department_id);NOTEXISTS操作符应用举例相关更新使用相关子查询依据一个表中的数据更新另一个表的数据UPDATEtable1alias1SETcolumn=(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);相关更新应用举例ALTERTABLEemployeesADD(department_nameVARCHAR2(14));UPDATEemployeeseSETdepartment_name=(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);DELETEFROMtable1alias1WHEREcolumnoperator(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);相关删除使用相关子查询依据一个表中的数据删除另一个表的数据DELETEFROMemployeesEWHEREemployee_id=(SELECTemployee_idFROMemp_historyWHEREemployee_id=E.employee_id);相关删除应用举例WITH子句使用WITH子句,可以避免在SELECT语句中重复书写相同的语句块WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中使用WITH子句可以提高查询效率WITH子句应用举例WITHdept_costsAS(SELECTd.department_name,SUM(e.salary)ASdept_totalFROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name),avg_costAS(SELECTSUM(dept_total)/COUNT(*)ASdept_avgFROMdept_costs)SELECT*FROMdept_costsWHEREdept_total(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;总结通过本章学习,您已经可以:使用多列子查询多列子查询的成对和非成对比较单列子查询相关子查询EXISTS和NOTEXISTS操作符相关更新和相关删除WITH子句