ZTESOracle多表查询第四讲.ppt

从多表中检索数据 有时，我需要查询的信息往往来自多个表。例如示例，报表显示了来自于两张表的数据， EMEPLOYEE_ID来自于EMPLOYEES表，DEPARTMENT_ID从EMPLOYEES表和DEPARTMENTS表都可以检索得到，而DEPARTMENT_NAME只能从DEPARTMENTS表中检索。要产生这样类似的报表，你需要连接访问EMPLOYEES表和DEPARTMENTS表。 连接类型 对于表的连接，使用基于1999 SQL的连接标准。 注意: 到现在为止所看到的连接都是使用Oracle的连接语法。Oracle语法的基础是ANSI SQL/86标准。在开发Oracle9i时，数据库还实现了ANSI SQL/92标准的连接语法。为了使用SQL全面兼容新的标准，应该在查询中使用SQL/92标准的语法。另外，将看到使用SQL/92语法是如何避免产生不必要的笛卡尔积的。 表的连接 语法分析: table1.column 指示被检索的数据所在的表和列 NATURAL JOIN 基于相同列名的连接 JOIN table USING column_name 指定实现等值连接的列名 JOIN table ON table1.column_name =table2.column_name 在ON子句中指定实现等值连接的条件 LEFT/RIGHT/FULL OUTER 外部连接的方式 CROSS JOIN 返回连个表的笛卡尔积 创建自然连接 使用NATURAL JOIN关键字可以实现具有列名和数据类型相匹配的两个表之间的自动连接。 注意： 如果 这种连接仅仅基于被连接的多个表中相同名称的列中数据的数据类型兼容的这种情况。如果相同名称的列中的数据的类型不兼容，那么就会返回自然连接的语法错误。 使用自然连接检索数据 在示例中，LOCATIONS表和DEPARTMENT表通过LOCATION_ID列进行连接，因为在这两张表中只有这一列名称相同。如果还存在相同名称的其他列，那么这些相同名称的列共同作为连接条件。 含有WHERE子句的自然连接： 在自然连接中通过使用WHERE子句添加限制。接下来的示例只显示20和50号部门的部门编号、部门名称、位置编号及所在城市： SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50); DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY ------------- ---------------- ----------- ------------------- 20 Marketing 1800 Toronto 50 Shipping 1500 South San Francisco 已选择2行。 USING子句 自然连接使用所有具有相同列名的列作为连接关系的。使用USING子句进行表连接，只需要指定查询必须是等连接的，并且等连接中的列必须同名。USING子句中引用的列在SQL语句中不能使用表名或别名作为前缀。 例如, 下面的SQL语句是有效的: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400; 接下来的语句是无效的，因为WHERE子句中的LOCATION_ID使用了表的别名作前缀： SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; ORA-25154: USING 子句的列部分不能有限定词 这个限制同样约束自然连接。因此，在自然和USING子句的连接中具有相同名称的列不能有前缀。 用于等连接的USING子句 确定每一个员工的部门名称，你可以通过员工表中每个员工的部门编号DEPARTMENT_ID在部门表中查找和与之相等的DEPARTMENT_ID所对应的部门名称DEPARTMENT_NAME。这种关系被称为等连接；故而，DEPARTMENT_ID列