Oracle-OCP-SQL-4-Displaying-Data-from-Multiple-Tab

Copyright©OracleCorporation,2001.Allrightsreserved.DisplayingDatafromMultipleTables4-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•WriteSELECTstatementstoaccessdatafrommorethanonetableusingequalityandnonequalityjoins•Viewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoins•Joinatabletoitselfbyusingaselfjoin4-3Copyright©OracleCorporation,2001.Allrightsreserved.ObtainingDatafromMultipleTablesEMPLOYEESDEPARTMENTS……4-4Copyright©OracleCorporation,2001.Allrightsreserved.CartesianProducts•ACartesianproductisformedwhen:–Ajoinconditionisomitted–Ajoinconditionisinvalid–Allrowsinthefirsttablearejoinedtoallrowsinthesecondtable•ToavoidaCartesianproduct,alwaysincludeavalidjoinconditioninaWHEREclause.4-5Copyright©OracleCorporation,2001.Allrightsreserved.GeneratingaCartesianProductCartesianproduct:20x8=160rowsEMPLOYEES(20rows)DEPARTMENTS(8rows)……4-6Copyright©OracleCorporation,2001.Allrightsreserved.•Equijoin•Non-equijoin•Outerjoin•SelfjoinTypesofJoins•Crossjoins•Naturaljoins•Usingclause•Fullortwosidedouterjoins•ArbitraryjoinconditionsforouterjoinsSQL:1999CompliantJoins:OracleProprietaryJoins(8iandprior):4-7Copyright©OracleCorporation,2001.Allrightsreserved.JoiningTablesUsingOracleSyntaxUseajointoquerydatafrommorethanonetable.•WritethejoinconditionintheWHEREclause.•Prefixthecolumnnamewiththetablenamewhenthesamecolumnnameappearsinmorethanonetable.SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;4-8Copyright©OracleCorporation,2001.Allrightsreserved.WhatisanEquijoin?EMPLOYEESDEPARTMENTSForeignkeyPrimarykey……4-9Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_idFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;RetrievingRecordswithEquijoins…4-10Copyright©OracleCorporation,2001.Allrightsreserved.AdditionalSearchConditionsUsingtheANDOperatorEMPLOYEESDEPARTMENTS……4-11Copyright©OracleCorporation,2001.Allrightsreserved.QualifyingAmbiguousColumnNames•Usetableprefixestoqualifycolumnnamesthatareinmultipletables.•Improveperformancebyusingtableprefixes.•Distinguishcolumnsthathaveidenticalnamesbutresideindifferenttablesbyusingcolumnaliases.4-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;UsingTableAliases•Simplifyqueriesbyusingtablealiases.•Improveperformancebyusingtableprefixes.4-13Copyright©OracleCorporation,2001.Allrightsreserved.JoiningMorethanTwoTablesEMPLOYEESLOCATIONSDEPARTMENTS•Tojoinntablestogether,youneedaminimumofn-1joinconditions.Forexample,tojointhreetables,aminimumoftwojoinsisrequired.…4-14Copyright©OracleCorporation,2001.Allrightsreserved.Non-EquijoinsEMPLOYEESJOB_GRADESSalaryintheEMPLOYEEStablemustbebetweenlowestsalaryandhighestsalaryintheJOB_GRADEStable.…4-15Copyright©OracleCorporation,2001.Allrightsreserved.RetrievingRecordswithNon-EquijoinsSELECTe.last_name,e.salary,j.grade_levelFROMemployeese,job_gradesjWHEREe.salaryBETWEENj.lowest_salANDj.highest_sal;…4-16Copyright©OracleCorporation,2001.Allrightsreserved.OuterJoinsEMPLOYEESDEPARTMENTSTherearenoemployeesindepartment190.…4-17Copyright©OracleCorporation,2001.Allrightsreserved.OuterJoinsSyntax•Youuseanouterjointoalsoseerowsthatdonotmeetthejoincondition.•TheOuterjoinoperatoristheplussign(+).SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);4-18Copyright©OracleCorporation,2001.Allrightsreserved.SELECTe.last_name,e.department_id,d.department_nameFROMemployeese,departmentsdWHEREe.department_id(+)=d.department_id;UsingOuterJoins…4-19Copyright©OracleCorporation,2001.Allrightsreserved.SelfJoinsEMPLOYEES(WORKER)EMPLOYEES(MANAGER)MANAGER_IDintheWORKERtableisequaltoEMPLOYEE_IDintheMANAGERtable.……4-20Copyright©OracleCorporation,2001.Allrightsreserved.JoiningaTabletoItselfSELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;…4-21Copyright©OracleCorporation,2001.Allrightsreserved.Practice4,PartOne:OverviewThispracticecoverswritingqueriestojointablestogetherusingOraclesyntax.4-22Copyright©OracleCorporation,2001.Allrightsreserved.JoiningTablesUsingSQL:1999SyntaxUseajointoquerydatafrommorethanonetable.SELECTtable1.column,table2.columnFROMtable1[CROSSJOINtable2]|[NATURALJOINtable2]|[JOINtable2USING(column_name)]|[JOINtable2ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)];4-23Copyright©OracleCorporation,2001.Allrightsreserved.CreatingCrossJoins•TheCROSSJOINclauseproducesthecross-productoftwotables.•ThisisthesameasaCartesianproductbetweenthetwotables.SELECTlast_name,department_nameFROMemployeesCROSSJOINdepartments;…4-