1. 内连接（inner join）：将两张表公共的部分连接起来（取两张表的交集：A∩B）
-- select <select_list> from table_a a inner join table_b b on a.key = b.key;
select u1.user_name, u1.role role1, u2.role role2 from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name;
-- 女帝 路飞の爱妻 王下七武海
1 inner join.png
2. 全外连接（full outer）
- A+B
-- select <select_list> from table_a a full outer join table_b b on a.key = b.key;（A+B）
-- 由于 MySql 中不支持全连接，所以使用 union all 来解决
select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name
union all
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name;
-- 女帝 路飞の爱妻 王下七武海
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
-- 女帝 路飞の爱妻 王下七武海
-- 红发 null 四皇成员一
-- 帝奇 null 四皇成员一
-- 百兽 null 四皇成员一
-- 玲玲 null 四皇成员一
2 full join 1.png
- (A+B)-(A∩B)
-- select <select_list> from table_a a full outer join table_b b on a.key = b.key where a.key is null or b.key is null;（(A+B)-(A∩B)）
-- 同理，也使用 union all 来解决
select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name where u2.role is null
union all
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name where u1.role is null;
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
-- 红发 null 四皇成员一
-- 帝奇 null 四皇成员一
-- 百兽 null 四皇成员一
-- 玲玲 null 四皇成员一
2 full join 2.png
3. 左外连接（left outer）
- 查询出来a表全部记录：A
-- select <select_list> from table_a a left join table_b b on a.key = b.key;（查询出来a表全部记录：A）
select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name;
-- 女帝 路飞の爱妻 王下七武海
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
3 left join 1.png
- 只存在于a表，不存在于b表中的数据：A-B
--select <select_list> from table_a a left join table_b b on a.key = b.key where b.key is null;（只存在于a表，不存在于b表中的数据：A-B）
select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name where u2.role is null;
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
3 left join 2.png
4. 右外连接（right outer）
- 查询出来b表全部记录：B
-- select <select_list> from table_a a right join table_b b on a.key = b.key;（查询出来a表全部记录：B）
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name;
-- 女帝 路飞の爱妻 王下七武海
-- 红发 null 四皇成员一
-- 帝奇 null 四皇成员一
-- 百兽 null 四皇成员一
-- 玲玲 null 四皇成员一
4 right join 1.png
- 只存在于b表，不存在于b表中的数据：B-A
-- select <select_list> from table_a a right join table_b b on a.key = b.key where a.key is null;（只存在于a表，不存在于b表中的数据：B-A）
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name where u1.role is null;
-- 红发 null 四皇成员一
-- 帝奇 null 四皇成员一
-- 百兽 null 四皇成员一
-- 玲玲 null 四皇成员一
4 right join 2.png
5. 交叉连接（cross），笛卡尔连接（A*B）
select u1.user_name, u1.role, u2.user_name, u2.role from user1 u1 cross join user2 u2;
-- 结果是 4*5=20 条数据
5 cross join.png
6. join的使用技巧：
假设将
两张表中同时存在的人物的role属性，在表1中 改为女儿岛国王，该如何操作？
update user1 set role = "女儿岛国王" where user1.user_name in
(select u2.user_name from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name);
-- 很容易想到上面这个语句，但是 MySql 不支持这个语句（1093 错误：不能更新在 from 从句中出现的表）
-- 这时，可以使用下面的方法进行更新
update user1 u1 join
(select u2.user_name from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name) tmp
on u1.user_name = tmp.user_name set u1.role = "女儿岛国王";
select * from user1;
-- 1 路飞 草帽の船长
-- 2 娜美 草帽航海士
-- 3 女帝 女儿岛国王
-- 4 乔巴 草帽の船医
6 join 从句使用技巧1.png
网友评论