Oracle-OCP-SQL-18-Advanced-Subqueries

Copyright©OracleCorporation,2001.Allrightsreserved.AdvancedSubqueries18-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Writeamultiple-columnsubquery•Describeandexplainthebehaviorofsubquerieswhennullvaluesareretrieved•WriteasubqueryinaFROMclause•UsescalarsubqueriesinSQL•Describethetypesofproblemsthatcanbesolvedwithcorrelatedsubqueries•Writecorrelatedsubqueries•Updateanddeleterowsusingcorrelatedsubqueries•UsetheEXISTSandNOTEXISTSoperators•UsetheWITHclause18-3Copyright©OracleCorporation,2001.Allrightsreserved.WhatIsaSubquery?AsubqueryisaSELECTstatementembeddedinaclauseofanotherSQLstatement.SELECT...FROM...WHERE...(SELECT...FROM...WHERE...)MainquerySubquery18-4Copyright©OracleCorporation,2001.Allrightsreserved.Subqueries•Thesubquery(innerquery)executesoncebeforethemainquery.•Theresultofthesubqueryisusedbythemainquery(outerquery).SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);18-5Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHEREemployee_id=149);UsingaSubquery1050018-6Copyright©OracleCorporation,2001.Allrightsreserved.Multiple-ColumnSubqueriesMainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery100901026012450Eachrowofthemainqueryiscomparedtovaluesfromamultiple-rowandmultiple-columnsubquery.18-7Copyright©OracleCorporation,2001.Allrightsreserved.ColumnComparisonsColumncomparisonsinamultiple-columnsubquerycanbe:•Pairwisecomparisons•Nonpairwisecomparisons18-8Copyright©OracleCorporation,2001.Allrightsreserved.PairwiseComparisonSubqueryDisplaythedetailsoftheemployeeswhoaremanagedbythesamemanagerandworkinthesamedepartmentastheemployeeswithEMPLOYEE_ID178or174.SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(178,174))ANDemployee_idNOTIN(178,174);18-9Copyright©OracleCorporation,2001.Allrightsreserved.NonpairwiseComparisonSubquerySELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))ANDemployee_idNOTIN(174,141);DisplaythedetailsoftheemployeeswhoaremanagedbythesamemanagerastheemployeeswithEMPLOYEE_ID174or141andworkinthesamedepartmentastheemployeeswithEMPLOYEE_ID174or141.18-10Copyright©OracleCorporation,2001.Allrightsreserved.SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salaryb.salavg;UsingaSubqueryintheFROMClause18-11Copyright©OracleCorporation,2001.Allrightsreserved.ScalarSubqueryExpressions•Ascalarsubqueryexpressionisasubquerythatreturnsexactlyonecolumnvaluefromonerow.•ScalarsubqueriesweresupportedinOracle8ionlyinalimitedsetofcases,Forexample:–SELECTstatement(FROMandWHEREclauses)–VALUESlistofanINSERTstatement•InOracle9i,scalarsubqueriescanbeusedin:–ConditionandexpressionpartofDECODEandCASE–AllclausesofSELECTexceptGROUPBY18-12Copyright©OracleCorporation,2001.Allrightsreserved.ScalarSubqueries:ExamplesScalarSubqueriesinCASEExpressionsSELECTemployee_id,last_name,(CASEWHENdepartment_id=THEN'Canada'ELSE'USA'END)locationFROMemployees;(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1800)ScalarSubqueriesinORDERBYClauseSELECTemployee_id,last_nameFROMemployeeseORDERBY20(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);18-14Copyright©OracleCorporation,2001.Allrightsreserved.CorrelatedSubqueriesCorrelatedsubqueriesareusedforrow-by-rowprocessing.Eachsubqueryisexecutedonceforeveryrowoftheouterquery.GETcandidaterowfromouterqueryEXECUTEinnerqueryusingcandidaterowvalueUSEvaluesfrominnerquerytoqualifyordisqualifycandidaterow18-15Copyright©OracleCorporation,2001.Allrightsreserved.CorrelatedSubqueriesSELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator(SELECTcolum1,column2FROMtable2WHEREexpr1=.expr2);Thesubqueryreferencesacolumnfromatableintheparentquery.outerouter18-16Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,salary,department_idFROMemployeesouterWHEREsalaryUsingCorrelatedSubqueriesEachtimearowfromtheouterqueryisprocessed,theinnerqueryisevaluated.Findallemployeeswhoearnmorethantheaveragesalaryintheirdepartment.(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);18-17Copyright©OracleCorporation,2001.Allrightsreserved.UsingCorrelatedSubqueriesSELECTe.employee_id,last_name,e.job_idFROMemployeeseWHERE2=(SELECTCOUNT(*)FROMjob_historyWHEREemployee_id=e.employee_id);Displaydetailsofthoseemployeeswhohaveswitchedjobsatleasttwice.18-18Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheEXISTSOperator•TheEXISTSoperatortestsforexistenceofrowsintheresultssetofthesubquery.•Ifasubqueryrowvalueisfound:–Thesearchdoesnotcontinueintheinnerquery–TheconditionisflaggedTRUE•Ifasubqueryrowvalueisnotfound:–TheconditionisflaggedFA