LES04显示多表数据 连接.pptVIP

显示多表数据 目标 使用相等连接 使用不等连接 使用自连接 使用外连接 获得多张表数据 什么是连接 连接：查询多张表数据 在WHERE子句中指定连接条件 同名列要加表名作为前缀 连接类型 什么是相等连接 使用相等连接检索数据 使用AND操作符指定其它条件 使用表别名 不等连接 使用不等连接检索数据 自连接 使用自连接 外连接 左外连接 右外连接 完全外连接 (+)操作符 左外连接 右外连接 完全外连接 使用(+)操作符 小结 习题 显示所有雇员姓名、部门代码以及所在部门名称 显示部门30的岗位列表以及部门位置 显示获得补助的雇员名称以及所在部门名 显示包含字符“A”的所有雇员名称及所在部门名称 显示在“DALLAS”工作的雇员名、岗位以及部门名 习题 显示雇员名、雇员代码，及其管理者的姓名和代码 查看SALGRADE结构，然后显示雇员名、岗位、工资以及工资级别 不等连接：指连接条件使用除＝外的其他比较操作符 Non-Equijoins The relationship between the EMP table and the SALGRADE table is a non-equijoin, meaning that no column in the EMP table corresponds directly to a column in the SALGRADE table. The relationship between the two tables is that the SAL column in the EMP table is between the LOSAL and HISAL column of the SALGRADE table. The relationship is obtained using an operator other than equal (=). Non-Equijoins (continued) The example above creates a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges. It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this: None of the rows in the salary grade table contain grades that overlap. That is, the salary value for an employee can only lie between the low salary and high salary values of one of the rows in the salary grade table. All of the employees’ salaries lie within the limits provided by the salary grade table. That is, no employee earns less than the lowest value contained in the LOSAL column or more than the highest value contained in the HISAL column Note: Other operators such as = and = could be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN. Table aliases have been specified for performance reasons, not because of possible ambiguity. (+)应放在数据相对较少的一端 Returning Records with No Direct Match with Outer Joins (continued) The example above displays numbers and names