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