Join语句
一、Join语句几种方式
1.内连接(INNER)
SELECT a.username,a.over,b.over FROM user1 a INNER JOIN user2 b ON a.username = b.username;
查询结果:a、b两表的公共数据
2.左外连接(LEFT OUTER)
SELECT a.username,a.over,b.over FROM user1 a LEFT JOIN user2 b ON a.username = b.username WHERE b.username IS NOT NULL;
查询结果:a表的所有数据
3.右外连接(RIGHT OUTER )
SELECT b.username,b.over,a.over FROM user1 a RIGHT JOIN user2 b ON a.username = b.username WHERE a.username IS NULL;
查询结果:b表的所有数据 - ab两个表的公共数据
4.全外连接(FULL OUTER)
SELECT a.username,a.over,b.over FROM user1 a FULL JOIN user2 b ON a.username = b.username WHERE a.username IS NULL OR b.username IS NULL;
MySQL中不支持FULL OUTER JOIN 连接,故上面查询会出现错误
SELECT a.username,a.over,b.over FROM user1 a LEFT JOIN user2 b ON a.username = b.username UNION ALL SELECT b.username,b.over,a.over FROM user1 a RIGHT JOIN user2 b ON a.username = b.username WHERE a.username IS NULL OR b.username IS NULL;
查询结果:a,b表的所有数据 - a,b表的公共数据
5.交叉连接(CROSS)或笛卡尔连接(cartesian)或叉乘(Product)
SELECT a.username,a.over,b.over FROM user1 a CROSS JOIN user2 b ON a.username = b.username
一般要避免使用此查询
二、Join语句的使用
1.使用join更新表
UPDATE user1 SET over = '齐天大圣' WHERE user1.username IN( SELECT b.username FROM user1 a JOIN user2 b ON a.username = b.username );
MySQL不支持UPDATE操作,故上面更新操作会出现错误
UPDATE user1 a JOIN ( SELECT b.username FROM user1 a JOIN user2 b ON a.username = b.username ) b ON a.username = b.username SET a.over='齐天大圣';
更新结果:将over字段的数据更新为‘齐天大圣’
2.使用join优化子查询
SELECT a.username,a.over, ( SELECT over FROM user2 b WHERE a.username = b.username ) AS over2 FROM user1 a;
查询结果:对a表中的每一条数据都进行子查询,很慢
优化后:
SELECT a.username,a.over,b.over AS over2 FROM user1 a LEFT JOIN user2 b ON a.username = b.username
优化后和优化前结果相同则为有效优化
查询结果:对a表中的每一条数据都进行子查询,较快
3.使用join优化聚合子查询
SELECT a.username,b.timestr,b.kills FROM user1 a JOIN userkills b ON a.id = b.userid WHERE b.kills = ( SELECT MAX(c.kills) FROM userkills c WHERE c.userid = b.userid );
查询结果:各个username打怪(kills)最多的日期
避免子查询:
SELECT a.username,b.timestr,b.kills FROM user1 a JOIN userkills b ON a.id = b.userid JOIN userkills c ON c.userid = b.userid GROUP BY a.username,b.timestr,b.kills HAVING b.kills = MAX(c.kills);