3-2 复杂查询-实现多表数据的访问

1、4Copyright Oracle Corporation, 2001. All rights reserved.从多表中显示数据从多表中显示数据4-2Copyright Oracle Corporation, 2001. All rights reserved.目标目标完成本课后, 您应当能够执行下列操作： 写SELECT 语句使用等值和非等值连接从多个表中访问数据 使用外连接查看不满足连接条件的数据 使用一个自连接，连接一个表到它自己4-3Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲 笛卡尔积 连接的类型 ORAC

2、LE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-4Copyright Oracle Corporation, 2001. All rights reserved.从多表中获得数据从多表中获得数据EMPLOYEES DEPARTMENTS 4-5Copyright Oracle Corpor

3、ation, 2001. All rights reserved.笛卡尔乘积笛卡尔乘积 笛卡尔乘积的形成，当： 一个连接条件被遗漏时 一个连接条件不正确时 在第一个表中的所有行被连接到第二个表的所有行时 为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包含正确的连接条件4-6Copyright Oracle Corporation, 2001. All rights reserved.笛卡尔乘积的产生笛卡尔乘积的产生笛卡尔乘积20 x8=160 rowsEMPLOYEES (20 rows)DEPARTMENTS (8 rows)4-7Copyright Oracle Corporati

4、on, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-8Copyright Oracle Corporation, 2001. All rights reserved.Equijoin 等值Non-

5、equijoin 非等值Outer join 外连接 Self join 自连接连接的类型连接的类型Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outerjoins 全连接或双向外连接Arbitrary join conditions forouter joins 对于外连接的任意连接条件SQL: 1999 适应连接适应连接:Oracle所有的连接所有的连接(8i 以前以前):4-9Copyright Oracle Corporation, 2001. All rights reserved.用用Or

6、acle 语法连接表语法连接表使用一个连接从多个表中查询数据在WHERE 子句中写连接条件.当多个表中有相同的列名时，将表名作为列名的前缀SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;4-10Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接S

7、QL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-11Copyright Oracle Corporation, 2001. All rights reserved.什么是等值连接什么是等值连接?EMPLOYEES DEPARTMENTS Foreign keyPrimary key4-12Copyright Oracle Corporation, 2001. All rights reserved.SELECT

8、employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;用等值连接返回记录用等值连接返回记录4-13Copyright Oracle Corporation, 2001. All rights reserved.使用使用AND 操作符附加搜索条件操

9、作符附加搜索条件EMPLOYEES DEPARTMENTS 4-14Copyright Oracle Corporation, 2001. All rights reserved.限制不明确的列名限制不明确的列名 在多表中使用表前缀限制修饰列名 用表前缀改善性能 用列别名区别有相同名称，但在不同表中的列4-15Copyright Oracle Corporation, 2001. All rights reserved.SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM e

10、mployees e , departments dWHERE e.department_id = d.department_id;使用表别名使用表别名 使用表别名简化查询 使用表别名改善性能.4-16Copyright Oracle Corporation, 2001. All rights reserved.多于两个表的连接多于两个表的连接EMPLOYEES LOCATIONS DEPARTMENTS 为了连接n 个表，你最少需要n-1 个连接条件。例如，为了连接3 个表，最少需要两个连接4-17Copyright Oracle Corporation, 2001. All rights

11、reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-18Copyright Oracle Corporation, 2001. All rights reserved.非等值连接非等值连接EMPLOYEESJOB_GRADES在EMPLO

12、YEES 表中的工资必须在JOB_GRADES 表中的最低工资和最高工资之间4-19Copyright Oracle Corporation, 2001. All rights reserved.用非等值连接返回记录用非等值连接返回记录SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;4-20Copyright Oracle Corporation, 2001. All rights rese

13、rved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-21Copyright Oracle Corporation, 2001. All rights reserved.外连接外连接EMPLOYEESDEPARTMENTS在部门190中无雇员4-2

14、2Copyright Oracle Corporation, 2001. All rights reserved.外连接语法外连接语法 你可以用一个外连接查看那些不满足连接条件的行 外连接运算符是加号(+)SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECT table1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);4-23Copyright O

15、racle Corporation, 2001. All rights reserved.SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ; 使用外连接使用外连接4-24Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Ou

16、ter join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-25Copyright Oracle Corporation, 2001. All rights reserved.自连接自连接EMPLOYEES (WORKER)EMPLOYEES (MANAGER)在WORKER 表中的MANAGER_ID等于MANAGER 表中的EMPLOYEE_ID4-26Copyrig

17、ht Oracle Corporation, 2001. All rights reserved.连接一个表到它本身连接一个表到它本身SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;4-27Copyright Oracle Corporation, 2001. All rights reserved.练习练习4, 第一部分第一部分: 概览概览这部分练习包括用Or

18、acle 语法写将表连接在一起的查询4-28Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-29Copyright Oracle Corpo

19、ration, 2001. All rights reserved.用用SQL 连接表连接表: 1999 语法语法用一个连接从多个表中查询数据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_nam

20、e = table2.column_name);4-30Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-31Copyright Oracle

21、 Corporation, 2001. All rights reserved.创建交叉连接创建交叉连接 CROSS JOIN 子句导致两个表的交叉乘积 该连接和两个表之间的笛卡尔乘积是一样的SELECT last_name, department_nameFROM employeesCROSS JOIN departments ;4-32Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self

22、join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-33Copyright Oracle Corporation, 2001. All rights reserved.创建自然连接创建自然连接 NATURAL JOIN 子句基于两个表之间有相同名字的所有列 它从两个表中选择在所有的匹配列中有相等值的行 如果有相同名字的列的数据类型不同，返回一个错误4-34Copyright Oracle Corpo

23、ration, 2001. All rights reserved.SELECT department_id, department_name, location_id, cityFROM departmentsNATURAL JOIN locations ;用自然连接返回记录用自然连接返回记录4-35Copyright Oracle Corporation, 2001. All rights reserved.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性

24、连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-36Copyright Oracle Corporation, 2001. All rights reserved.用用USING 子句创建连接子句创建连接 如果一些列有相同的名字，但数据类型不匹配，NATURAL JOIN 子句能够用USING 子句修改以指定将被用于一个等值连接的列 当有多个列匹配时，用USING 子句匹配唯一的列 在引用列不要使用表名或者别名 NATURAL JO

25、IN 和USING 子句是相互排斥的4-37Copyright Oracle Corporation, 2001. All rights reserved.SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;用用USING 子句返回记录子句返回记录4-38Copyright Oracle Corporation, 2001. All rights reserved.用用ON 子句创建连接子句创建连接 对于自然连接的连接条件，基本上是带有

26、相同名字的所有列的等值连接 为了指定任意条件，或者指定要连接的列，可以使用ON 子句 连接条件从另一个搜索条件中被分开 ON 子句使得代码易懂4-39Copyright Oracle Corporation, 2001. All rights reserved.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_id);用用ON 子句返回记录子句

27、返回记录4-40Copyright Oracle Corporation, 2001. All rights reserved.用用ON 子句创建三向连接子句创建三向连接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;4-41Copyright Oracle Corporation, 2001. All rights re

28、served.大纲大纲笛卡尔积 连接的类型 ORACLE的所有连接Equijoin 等值Non-equijoin 非等值Outer join 外连接 Self join 自连接SQL：1999适应性连接Cross joins 交叉连接Natural joins 自然连接Using clause 使用子句Full or two sided outer joins 全连接或双向外连接对于外连接的任意连接条件4-42Copyright Oracle Corporation, 2001. All rights reserved.内与外连接内与外连接 在SQL: 1999 中，连接两个表，仅返回匹配的行

29、的连接，称为内连接 在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接 在两个表之间的连接，返回内连接的结果， 同时还返回左和右连接，称为全连接4-43Copyright Oracle Corporation, 2001. All rights reserved.SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;左外连接左外连接4-44Copyright Oracle Corporation, 2001. All rights reserved.SELECT e.last_name,