oracle 10g多表查询.ppt

Example of FULL OUTER JOIN This query retrieves all rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table. Instructor Note It was not possible to complete this in earlier releases using outer joins. However, you could accomplish the same results using the UNION operator. SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id (+) = d.department_id UNION SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id (+); SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ; RIGHT OUTER JOIN … SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; FULL OUTER JOIN … Schedule: Timing Topic 55 minutes Lecture 55 minutes Practice 110 minutes Total Lesson Aim This lesson covers how to obtain data from more than one table. Data from Multiple Tables Sometimes you need to use data from more than one table. In the slide example, the report displays data from two separate tables. Employee IDs exist in the EMPLOYEES table. Department IDs exist in both the EMPLOYEES and DEPARTMENTS tables. Location IDs exist in the DEPARTMENTS table. To produce the report, you need to link the EMPLOYEES and DEPARTMENTS tables and access data from both of them. Cartesian Products When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. All rows in the first table are joined to all rows in the second table. A Cartesian product tends to generate a large number of rows, and the result is rarely useful. You should always include a valid join