吉林大学数据库技术与Oracle sql-06.ppt

Subqueries

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribethetypesofproblemthatsubqueriescansolveDefinesubqueriesListthetypesofsubqueriesWritesingle-rowandmultiple-rowsubqueries

UsingaSubquery

toSolveaProblemWhohasasalarygreaterthanAbel’s?WhichemployeeshavesalariesgreaterthanAbel’ssalary?MainQuery:?WhatisAbel’ssalary??Subquery

SubquerySyntaxThesubquery(innerquery)executesoncebeforethemainquery.Theresultofthesubqueryisusedbythemainquery(outerquery).SELECT select_listFROM tableWHERE exproperator (SELECT select_list FROM table);

SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHERElast_name=Abel);UsingaSubquery11000

GuidelinesforUsingSubqueriesEnclosesubqueriesinparentheses.Placesubqueriesontherightsideofthecomparisoncondition.TheORDERBYclauseinthesubqueryisnotneededunlessyouareperformingtop-nanalysis.Usesingle-rowoperatorswithsingle-rowsubqueriesandusemultiple-rowoperatorswith

multiple-rowsubqueries.

TypesofSubqueriesMainquerySubqueryreturnsST_CLERKMultiple-rowsubqueryST_CLERKSA_MANMainquerySubqueryreturnsSingle-rowsubquery

Single-RowSubqueriesReturnonlyonerowUsesingle-rowcomparisonoperatorsOperator== = MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto

SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary(SELECTsalaryFROMemployeesWHEREemployee_id=143);ExecutingSingle-RowSubqueriesST_CLERK2600

SELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);UsingGroupFunctionsinaSubquery2500

TheHAVIN