本文共 3413 字，大约阅读时间需要 11 分钟。

Join 操 作 Join操作基本分为3大类：外连接(细分为：左连接、右连接、全连接)、自然连接、内连接 Join操作的共性：第一步均为将所有参与操作的表进行了一个笛卡儿积，然后才依据各连接 条件进行记录的筛选 SQL> select * from employees; LAST_NAME DEPARTMENT_ID SALARY ------------------------------ ------------------------ ------------ Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 Kochhar 5000 SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NAME ------------------------ ------------------------------ 10 Sales 20 Marketing 30 Accounts 40 Administration --------------------------------------------------Left outer join-------------------------------------------------- SQL> select * from employees e left outer join departments d 2 on e.department_id=d.department_id; LAST_NAME DEPARTMENT_ID SALARY DEPARTMENT_ID DEPARTMENT_NAME ----------------- ------------------------ ----------- ----------------------- ------------------------------ Getz 10 3000 10 Sales King 20 2200 20 Marketing Davis 20 1500 20 Marketing Davis 30 5000 30 Accounts Kochhar 5000 附加：Oracle9i以前版本中左连接的写法如下： SQL> select * from employees e ，departments d 2 on e.department_id（+）=d.department_id; -------------------------------------------------Right outer join------------------------------------------------- SQL> select * from employees right outer join departments 2 using(department_id); DEPARTMENT_ID LAST_NAME SALARY DEPARTMENT_NAME ----------------------- ----------------- ---------- ------------------------------ 10 Getz 3000 Sales 20 Davis 1500 Marketing 20 King 2200 Marketing 30 Davis 5000 Accounts 40 Administration 附加：Oracle9i以前版本中左连接的写法如下： SQL> select * from employees e ，departments d 2 where e.department_id=d.department_id(+); --------------------------------------------------Full outer join-------------------------------------------------- SQL> select * from employees full outer join departments 2 using(department_id); DEPARTMENT_ID LAST_NAME SALARY DEPARTMENT_NAME ------------------------ ------------------- ---------- ----------------------------- 10 Getz 3000 Sales 20 King 2200 Marketing 20 Davis 1500 Marketing 30 Davis 5000 Accounts Kochhar 5000 40 Administration 说明：[1]外连接必须使用on或using子句提供相应的连接条件 [2]不能为using子句中所列举的列指定表别名，即使在group by和select子句中也是如此 [3]外连接规则：左连右补，右连左补，全连左右合并 如：对表departments表进行右连接时，在两表完成笛卡儿积后再依据连接条件 using(department_id)来筛选两表中department_id值相同的记录，但对DEPARTMENT_ID=40 employees表中没有与之匹配的记录，按常理此DEPARTMENT_ID=40所对应的记录将被抛 弃，但就是为了保全连接表（departments表）中的所有记录就必须在employees表中虚拟出 一条与之匹配的记录来保全连接表的所有记录，当然这条虚拟的记录显示时值全为null --------------------------------------------------Natural join-------------------------------------------------- SQL> select * from employees natural join departments; DEPARTMENT_ID LAST_NAME SALARY DEPARTMENT_NAME ------------------------ ------------------ ---------- ------------------------------ 10 Getz 3000 Sales 20 Davis 1500 Marketing 20 King 2200 Marketing 30 Davis 5000 Accounts 说明：自然连接是通过对参与表关系中所有同名的属性对取等（即相等比较）来完成的，故无须自己添加连接条件 与外连接的区别在于对于无法匹配的记录外连接会虚拟一条与之匹配的记录来保全连接表中的所有记录，但自然连接不会 --------------------------------------------------Inner join-------------------------------------------------- SQL> select * from employees inner join departments 2 using(department_id); DEPARTMENT_ID LAST_NAME SALARY DEPARTMENT_NAME ------------------------ ----------------- ---------- ------------------------------ 10 Getz 3000 Sales 20 Davis 1500 Marketing 20 King 2200 Marketing 30 Davis 5000 Accounts 说明：内连接与自然连接基本相同，不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件

转载地址：https://blog.csdn.net/leslie_moon/article/details/83434031 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！