Oracle Pl SQL 04

DisplayingDatafromMultipleTables4-2ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•WriteSELECTstatementstoaccessdatafrommorethanonetableusingequalityandnonequalityjoins•Viewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoins•Joinatabletoitselfbyusingaselfjoin4-3ObtainingDatafromMultipleTablesEMPLOYEESDEPARTMENTS……4-4CartesianProducts•ACartesianproductisformedwhen:–Ajoinconditionisomitted–Ajoinconditionisinvalid–Allrowsinthefirsttablearejoinedtoallrowsinthesecondtable•ToavoidaCartesianproduct,alwaysincludeavalidjoinconditioninaWHEREclause.4-5GeneratingaCartesianProductCartesianproduct:20x8=160rowsEMPLOYEES(20rows)DEPARTMENTS(8rows)……4-6•Equijoin•Non-equijoin•Outerjoin•SelfjoinTypesofJoins•Crossjoins•Naturaljoins•Usingclause•Fullortwosidedouterjoins•ArbitraryjoinconditionsforouterjoinsSQL:1999CompliantJoins:OracleProprietaryJoins(8iandprior):4-7JoiningTablesUsingOracleSyntaxUseajointoquerydatafrommorethanonetable.•WritethejoinconditionintheWHEREclause.•Prefixthecolumnnamewiththetablenamewhenthesamecolumnnameappearsinmorethanonetable.SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;4-8WhatisanEquijoin?EMPLOYEESDEPARTMENTSForeignkeyPrimarykey……4-9SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;RetrievingRecordswithEquijoins…4-10QualifyingAmbiguousColumnNames•Usetableprefixestoqualifycolumnnamesthatareinmultipletables.•Improveperformancebyusingtableprefixes.•Distinguishcolumnsthathaveidenticalnamesbutresideindifferenttablesbyusingcolumnaliases.4-11SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;UsingTableAliases•Simplifyqueriesbyusingtablealiases.•Improveperformancebyusingtableprefixes.4-12JoiningMorethanTwoTablesEMPLOYEESLOCATIONSDEPARTMENTS•Tojoinntablestogether,youneedaminimumofn-1joinconditions.Forexample,tojointhreetables,aminimumoftwojoinsisrequired.…4-13Non-EquijoinsEMPLOYEESJOB_GRADESSalaryintheEMPLOYEEStablemustbebetweenlowestsalaryandhighestsalaryintheJOB_GRADEStable.…4-14RetrievingRecordswithNon-EquijoinsSELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;…4-15OuterJoinsEMPLOYEESDEPARTMENTSTherearenoemployeesindepartment190.…4-16OuterJoinsSyntax•Youuseanouterjointoalsoseerowsthatdonotmeetthejoincondition.•Theouterjoinoperatoristheplussign(+).SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);4-17SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;UsingOuterJoins…4-18SelfJoinsEMPLOYEES(WORKER)EMPLOYEES(MANAGER)MANAGER_IDintheWORKERtableisequaltoEMPLOYEE_IDintheMANAGERtable.……4-19JoiningaTabletoItselfSELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;…4-20SummaryInthislesson,youshouldhavelearnedhowtousejoinstodisplaydatafrommultipletablesin:•Oracleproprietarysyntaxforversions8iandearlier•SQL:1999compliantsyntaxforversion9i