Oracle Pl SQL 04

DisplayingDatafromMultipleTablesObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:WriteSELECTstatementstoaccessdatafrommorethanonetableusingequalityandnonequalityjoinsViewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoinsJoinatabletoitselfbyusingaselfjoinObtainingDatafromMultipleTablesEMPLOYEESDEPARTMENTS……CartesianProductsACartesianproductisformedwhen:AjoinconditionisomittedAjoinconditionisinvalidAllrowsinthefirsttablearejoinedtoallrowsinthesecondtableToavoidaCartesianproduct,alwaysincludeavalidjoinconditioninaWHEREclause.GeneratingaCartesianProductCartesianproduct:20x8=160rowsEMPLOYEES(20rows)DEPARTMENTS(8rows)……EquijoinNon-equijoinOuterjoinSelfjoinTypesofJoinsCrossjoinsNaturaljoinsUsingclauseFullortwosidedouterjoinsArbitraryjoinconditionsforouterjoinsSQL:1999CompliantJoins:OracleProprietaryJoins(8iandprior):JoiningTablesUsingOracleSyntaxUseajointoquerydatafrommorethanonetable.WritethejoinconditionintheWHEREclause.Prefixthecolumnnamewiththetablenamewhenthesamecolumnnameappearsinmorethanonetable.SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;WhatisanEquijoin?EMPLOYEESDEPARTMENTSForeignkeyPrimarykey……SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;RetrievingRecordswithEquijoins…QualifyingAmbiguousColumnNamesUsetableprefixestoqualifycolumnnamesthatareinmultipletables.Improveperformancebyusingtableprefixes.Distinguishcolumnsthathaveidenticalnamesbutresideindifferenttablesbyusingcolumnaliases.SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;UsingTableAliasesSimplifyqueriesbyusingtablealiases.Improveperformancebyusingtableprefixes.JoiningMorethanTwoTablesEMPLOYEESLOCATIONSDEPARTMENTSTojoinntablestogether,youneedaminimumofn-1joinconditions.Forexample,tojointhreetables,aminimumoftwojoinsisrequired.…Non-EquijoinsEMPLOYEESJOB_GRADESSalaryintheEMPLOYEEStablemustbebetweenlowestsalaryandhighestsalaryintheJOB_GRADEStable.…RetrievingRecordswithNon-EquijoinsSELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;…OuterJoinsEMPLOYEESDEPARTMENTSTherearenoemployeesindepartment190.…OuterJoinsSyntaxYouuseanouterjointoalsoseerowsthatdonotmeetthejoincondition.Theouterjoinoperatoristheplussign(+).SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;UsingOuterJoins…SelfJoinsEMPLOYEES(WORKER)EMPLOYEES(MANAGER)MANAGER_IDintheWORKERtableisequaltoEMPLOYEE_IDintheMANAGERtable.……JoiningaTabletoItselfSELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;…SummaryInthislesson,youshouldhavelearnedhowtousejoinstodisplaydatafrommultipletablesin:Oracleproprietarysyntaxforversions8iandearlierSQL:1999compliantsyntaxforversion9i演讲完毕，谢谢观看！