oracle多表查詢學習教案

1、會計學1oracle多表查詢多表查詢(chxn)第一頁，共41頁。第1頁/共41頁第二頁，共41頁。EMPLOYEES DEPARTMENTS 第2頁/共41頁第三頁，共41頁。第3頁/共41頁第四頁，共41頁。笛卡爾集笛卡爾集: 20 x8=160行行EMPLOYEES (20行行)DEPARTMENTS (8行行)第4頁/共41頁第五頁，共41頁。適用適用(shyng)于于SQL: 1999的連接的連接:Oracle 提供提供(tgng)的的連連接接 (8i 或更早或更早): 第5頁/共41頁第六頁，共41頁。SELECTtable1.column, table2.columnFROMta

2、ble1, table2WHEREtable1.column1 = table2.column2;第6頁/共41頁第七頁，共41頁。EMPLOYEES DEPARTMENTS 外鍵外鍵主鍵主鍵第7頁/共41頁第八頁，共41頁。 SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = d

3、epartments.department_id;第8頁/共41頁第九頁，共41頁。EMPLOYEES DEPARTMENTS 第9頁/共41頁第十頁，共41頁。第10頁/共41頁第十一頁，共41頁。SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id;第11頁/共41頁第十二頁，共41頁。EMPLOYEES LOCATIONS DEPARTM

4、ENTS 第12頁/共41頁第十三頁，共41頁。EMPLOYEESJOB_GRADESEMPLOYEES表中的列工資表中的列工資應在應在JOB_GRADES表中的最高表中的最高工資與最低工資之間工資與最低工資之間第13頁/共41頁第十四頁，共41頁。SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;第14頁/共41頁第十五頁，共41頁。EMPLOYEESDEPARTMENTS190號部門沒有員

5、工號部門沒有員工 第15頁/共41頁第十六頁，共41頁。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);第16頁/共41頁第十七頁，共41頁。SELECT e.last_name, e.department_id, d.department_nameFROM employees e, d

6、epartments dWHERE e.department_id(+) = d.department_id ; 第17頁/共41頁第十八頁，共41頁。EMPLOYEES (WORKER)EMPLOYEES (MANAGER)WORKER 表中的表中的MANAGER_ID 和和 MANAGER 表中的表中的MANAGER_ID相等相等第18頁/共41頁第十九頁，共41頁。SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manag

7、er_id = manager.employee_id ;第19頁/共41頁第二十頁，共41頁。SELECTtable1.column, table2.columnFROMtable1CROSS JOIN table2 |NATURAL JOIN table2 |JOIN table2 USING (column_name) |JOIN table2 ON(table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)

8、;第20頁/共41頁第二十一頁，共41頁。 SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;第21頁/共41頁第二十二頁，共41頁。第22頁/共41頁第二十三頁，共41頁。 SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;第23頁/共41頁第二十四頁，共41頁。nNATURAL JOIN 和USING 子句經常同時使用。第24頁/共41頁第二十五頁，共41頁。

9、 SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;第25頁/共41頁第二十六頁，共41頁。第26頁/共41頁第二十七頁，共41頁。 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_

10、id);第27頁/共41頁第二十八頁，共41頁。 SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;第28頁/共41頁第二十九頁，共41頁。n兩個表在連接(linji)過程中除了返回滿足連接(linji)條件的行以外還返回兩個表中不滿足條件的行 ，這種連接(linji)稱為滿 外聯接。第29頁/共41頁第三十頁，共41頁。

11、SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;第30頁/共41頁第三十一頁，共41頁。 SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;

12、第31頁/共41頁第三十二頁，共41頁。 SELECT e.last_name, e.department_id, d.department_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id) ;第32頁/共41頁第三十三頁，共41頁。 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.departm