Mysql开发技巧之Join从句

1.Inner Join 内连接

内连接InnerJoin基于连接谓词将两张表的列组合在一起，产生新的结果表

selct * from A inner join b on A.key=b.key 

2.Left Outer Join左外连接（左连接）

select * from A left join B on A.key=B.key

(查出所有A表记录，不能关联B表的数据为NULL)

select * from A left join B on A.key=B.key where B.key is null

(查出A表 中存在且B表中不存在的记录，可以替换NOT IN，NOT IN 没有使用索引，)

3、Right Outer Join 右外连接（右连接）

select * from A right join B on A.key=B.key 

(查出所有B表记录，不能关联A表的数据为NULL)

select * from A right join B on A.key=B.key where B.key is null 

(查出B表 中存在且A表中不存在的记录，可以替换NOT IN，NOT IN 没有使用索引，)

4.Full Outer Join 全外连接

select * from A full  join B  on A.key=B.key 

（查出所有AB表中的记录，连接谓词中没有对应的用null进行填充）

select * from A full  join B on A.key=B.key where a.key is null or b.key is null 

(查出只存在AB表中的记录，且过滤AB公共的部分)
mysql不支持full join，使用 union all 连接左右连接查询

select * from A left join B on A.key=B.key union all select * from A right join B on A.key=B.key 

5.Cross Join

交叉连接(cross join) ，笛卡尔连接(cartesian join) 或叉乘（Product），两个表的乘积 A ×B,

select a.username,a.over,b.user_name,b.over from user1 a cross join user2 b 

使用技巧

1、如何更新使用过滤条件中包括自身的表

update user1 set over=’情天大圣’ where user1.username in (select b,username from user1 a inner join user2 b on a.username=b.username );
报错,优化如下：
update user1 a join (select b.username from user1 a inner join user2 b on a.username = b.username) b on a.username=b.username set a.over=’情天大圣’;

2.如何使用join 优化子查询

select a.username ,a.over , (select over from user2 b where a.username=b.username) as over2 from user1 a;

优化：
select a.username,a.over,b.over as over2 from user1 a left join user2 b on a.username=b.username;

3.使用join优化聚合子查询

select a.username,b.timestr,b.kills from user1 a join user_kills b on a.id=b.userid where b.kills = (select max(c.kills) from user_kills c where c.userid =b.userid)

优化：

select a.username,b.timestr,b.kills from user1 a join user_kills b on a.id=b.userid join user_kills c on c.userid=b.userid group by a.username.b.timestr,b.kills having b.kills =max(c.kills)

4.如何实现分组选择 （应用场景，取出所有分类中点击量最多的头三条文章。。。）

查出取经四人组每人杀怪最多的头两天
对每个人分别执行下面的查询：

select a.username,b.timestr,b.kills from user1 a join user_kills b on a.id = b.userid where username=”孙悟空” order by b.kills desc limit 2;

优化：
select d.username,c.timestr,kills from (select userid timestr,kills,(select count(*) from user_kills b where b.userid=a.userid and a.kills <= b.kills ) as cnt from user_kills a group by userid,timestr,kills ) c join user1 d on c.user_id =d.id where cnt<=2