Oracle Pl SQL 0410837

Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,4-,*,Displaying Data from Multiple Tables,Objectives,After completing this lesson,you should be able to,do the following:,Write,SELECT,statements to access data from more than one table using equality and nonequality joins,View data that generally does not meet a join condition by using outer joins,Join a table to itself by using a self join,Obtaining Data from Multiple Tables,EMPLOYEES,DEPARTMENTS,Cartesian Products,A Cartesian product is formed when:,A join condition is omitted,A join condition is invalid,All rows in the first table are joined to all rows in the second table,To avoid a Cartesian product,always include a valid join condition in a,WHERE,clause.,Generating a Cartesian Product,Cartesianproduct:20 x8=160 rows,EMPLOYEES,(20 rows),DEPARTMENTS,(8 rows),Equijoin,Non-equijoin,Outer join,Self join,Types of Joins,Cross joins,Natural joins,Using clause,Full or two sided outer joins,Arbitrary join conditions for outer joins,SQL:1999,Compliant Joins:,Oracle Proprietary,Joins(8,i,and prior):,Joining Tables Using Oracle Syntax,Use a join to query data from more than one table.,Write the join condition in the,WHERE,clause.,Prefix the column name with the table name when the same column name appears in more than one table.,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column1,=,table2.column2;,What is an Equijoin?,EMPLOYEES,DEPARTMENTS,Foreign key,Primary key,SELECT employees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_id,FROM employees,departments,WHERE employees.department_id=departments.department_id;,Retrieving Records with Equijoins,Qualifying Ambiguous Column Names,Use table prefixes to qualify column names that are in multiple tables.,Improve performance by using table prefixes.,Distinguish columns that have identical names but reside in different tables by using column aliases.,SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id,FROM employees e,departments d,WHERE e.department_id=d.department_id;,Using Table Aliases,Simplify queries by using table aliases.,Improve performance by using table prefixes.,Joining More than Two Tables,EMPLOYEES,LOCATIONS,DEPARTMENTS,To join,n,tables together,you need a minimum of n-1 join conditions.For example,to join three tables,a minimum of two joins is required.,Non-Equijoins,EMPLOYEES,JOB_GRADES,Salary in the,EMPLOYEES,table must be between,lowest salary and highest,salary in the,JOB_GRADES,table.,Retrieving Records with Non-Equijoins,SELECT e.last_name,e.salary,j.grade_level,FROM employees e,job_grades j,WHERE e.salary,BETWEEN j.lowest_sal AND j.highest,_sal;,Outer Joins,EMPLOYEES,DEPARTMENTS,There are no employees in department 190.,Outer Joins Syntax,You use an outer join to also see rows that do not meet the join condition.,The outer join operator is the plus sign(+).,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column,(+),=,table2.column;,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column,=,table2.column,(+),;,SELECT e.last_name,e.department_id,d.department_name,FROM employees e,departments d,WHERE e.department_id(+)=d.department_id;,Using Outer Joins,Self Joins,EMPLOYEES(WORKER),EMPLOYEES(MANAGER),MANAGER_ID,in the,WORKER,table is equal to,EMPLOYEE_ID,in the,MANAGER,table.,Joining a Table to Itself,SELECT worker.last_name|works for,|manager.last_name,FROM employees worker,employees manager,WHERE worker.manager_id=manager.employee_id;,Summary,In this lesson,you should have learned how to use,joins to display data from multiple tables in:,Oracle proprietary syntax for versions 8,i,and earlier,SQL:1999 compliant syntax for version 9,i,演讲完毕，谢谢观看！,