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

DisplayingData

fromMultipleTables

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:WriteSELECTstatementstoaccessdatafrommorethanonetableusingequalityandnonequalityjoinsViewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoinsJoinatabletoitselfbyusingaselfjoin

ObtainingDatafromMultipleTablesEMPLOYEESDEPARTMENTS……

CartesianProductsACartesianproductisformedwhen:AjoinconditionisomittedAjoinconditionisinvalidAllrowsinthefirsttablearejoinedtoallrowsinthesecondtableToavoidaCartesianproduct,alwaysincludeavalidjoinconditioninaWHEREclause.

GeneratingaCartesianProductCartesian

product:

20x8=160rowsEMPLOYEES(20rows)DEPARTMENTS(8rows)……

EquijoinNon-equijoinOuterjoinSelfjoinTypesofJoinsCrossjoinsNaturaljoinsUsingclauseFullortwosidedouterjoinsArbitraryjoinconditionsforouterjoinsSQL:1999CompliantJoins:OracleProprietaryJoins(8iandprior):

JoiningTablesUsingOracleSyntaxUseajointoquerydatafrommorethanonetable.WritethejoinconditionintheWHEREclause.Prefixthecolumnnamewiththetablenamewhenthesamecolumnnameappearsinmorethanonetable.SELECT table1.column,table2.columnFROM table1,table2WHERE table1.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;RetrievingRecords

withEquijoins…

QualifyingAmbiguous

ColumnNamesUsetableprefixestoqualifycolumnnamesthatareinmultipletables.Improveperformancebyusingtableprefixes.Distinguishcolumnsthathaveidenticalnamesbutresideindifferenttablesbyusingcolumnaliases.

SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemploy