多表连接
连接类型:
- Cross joins 交叉连接 -> 笛卡尔乘积
- Natural joins 自然连接
- USING clause 使用USING子句创建连接
- ON clause 使用ON子句
重点理解外连接
Natural自然连接
- 基于两个表中具有相同名称的所有列
- 从两个表中选择所有匹配列中值相等的行
- 若具有相同名称的列但数据类型不同,返回错误 i.e. 有隐式转换
自然连接中使用的列不能有限定符,即:对于查询的列不用加上前缀表名。
USING Clause
- 如果多个列具有相同的名称,当数据类型不匹配,则可以使用
- 不要再引用的列中使用表名或别名
限定不明确的列名
- 使用表前缀限定多个表中的列名
- 使用表前缀提高性能
- 使用列别名来区分名称相同但位于不同表中的列
- 不要在USING子句中使用出现过的别名
使用表别名Table Aliases
- 简化查询
- 提高性能
使用ON子句创建连接
- 使用ON子句指定任意条件或指定要连接的列
select e.employee_id,e.last_name,e.department_id,
d.department_id,d.location_id
from hr.employees e join hr.departments d
on e.department_id = d.department_id;
使用ON子句的自连接
select e.last_name emp, m.last_name mgr
from hr.employees e join hr.employees m
on (e.manager.id = m.employee_id)
使用ON子句创建三向连接
非对等连接 Nonequijoins
外连接与内连接
外连接分为左连接和右连接
eg. 合并顾客表和订单表 ,用INNER JOIN
SELECT
c.customer_id, c.first_name,o.order_id
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
ORDER BY customer_id
//if LEFT JOIN 可以选择到所有customers 无论是否有oder 不论on之后的条件,以左表customers c为基础
//if RIGHT JOIN 可以选择到所有orders o 不论on之后的条件 -> 此条件下等同于INNER JOIN
这样是INNER JOIN ,只展示有订单的顾客(及其订单),也就是两张表的交集
若展示全部顾客则改用LEFT (OUTER) JOIN, 结果相较于INNER JOIN多了没有订单的顾客
若要展示全部订单(及其顾客),就应该是 orders RIGHT JOIN customers,结果相较于 INNER JOIN
多了没有顾客的那些订单,即只有订单信息没有顾客信息的记录。(注:因为这里所有订单都有顾客,所以这里 RIGHT JOIN 结果和 INNER JOIN 一样)
笛卡尔积
- 省略了连接条件
- 连接条件无效