MySql 之 join 相关（1）

MySql 之 join 相关（1）

先准备几张表

李四的家庭表
table1

id	username	job
1	李四他妈	医生
2	李四他爸	司机
3	李四他哥	程序猿
4	李四	大学学生
5	李四他妹	高中学生

李四的班级
table2

id	username	score
1	李四	68
2	李四左同桌	70
3	李四前排	90
4	李四后排	53
5	李四右同桌	100

---

join 分类

• 内连接 inner，
  基于连接谓词将两张表的列组合在一起，产生新的结果表（两张表的公共部分）
• 全外连接 full outer
  
  • 查询所有在A表和B表中的数据。
    
    • select select_list from tableA as A full outer join tableB as B on A.key = B.key；
  • 过滤掉A和B表的公共部分
    
    • select select_list from tableA as A full outer join tableB as B on A.key = B.key where A.key is null or B.key is null；
  • (mySql 并不支持full join, 使用union all 达到 full join 效果)
    
    • select select_list from tableA as A left join tableB as B on A.key = B.key
      union all
      select select_list (注意和上面select_list对比) from tableA as A right join tableB as B on A.key = B.key;
• 左外连接 left outer
  
  • 包含前一张表的所有记录
    
    • select select_list from tableA as A left join tableB as B on A.key = B.key;
  • 还可以使用另外一种方式来查询仅在A表不在B表中的数据
    
    • select select_list form tableA as A left join tableB as B on A.key = B.key where B.key is null;
      (这种方式可以对 not in 进行优化，not in 是不会进行索引的)
• 右外连接 right outer
  
  • 和 左外连接 相反。
• 交叉连接 cross
  
  • 右称为笛卡尔连接，（tableA X tableB）使用这种连接 不需要提供连接关键词(ON) ，在实际使用中，尽量避免使用。

---

• 内连接，（查询李四的工作 和 分数）

      select a.username, a.job, b.score from table1 a join table2 b on a.username = b.username;

  查询结果

  '李四', '大学学生', '68'

  1. 左外连接, （查询李四家庭成员中，不在李四班级的人员相关信息）

  select a.username, a.job, b.score from table1 a left join table2 b on a.username = b.username where b.username is null;

  查询结果

  '李四他妈','医生',NULL
  '李四他爸','司机',NULL
  '李四他哥','程序猿',NULL
  '李四他妹','高中学生',NULL

  1. 右外连接 ，（查询李四的班级不在李四家庭里面的人）

  select b.username, a.job, b.score from table1 a right join table2 b on a.username = b.username where a.username is null;

  查询结果

  '李四左同桌',NULL,'70'
  '李四前排',NULL,'90'
  '李四后排',NULL,'53'
  '李四右同桌',NULL,'100'

  1. 全连接，（mySql 不支持 full join ,使用union all 来完成full join 的效果）

  select a.username, a.job, b.score from table1 a left join table2 b on a.username = b.username 
  union all
  select b.username, a.job, b.score from table1 a right join table2 b on a.username = b.username;

  查询结果

  '李四','大学学生','68'
  '李四他妈','医生',NULL
  '李四他爸','司机',NULL
  '李四他哥','程序猿',NULL
  '李四他妹','高中学生',NULL
  '李四','大学学生','68'
  '李四左同桌',NULL,'70'
  '李四前排',NULL,'90'
  '李四后排',NULL,'53'
  '李四右同桌',NULL,'100'

  1. 交叉连接，笛卡尔积连接，相当于 table1 乘以 table2 的结果，不需要提供连接关键词ON。

  select a.username, a.job, b.username, b.score from table1 a cross join table2 b;

  查询结果

  '李四他妈','医生','李四','68'
  '李四他爸','司机','李四','68'
  '李四他哥','程序猿','李四','68'
  '李四','大学学生','李四','68'
  '李四他妹','高中学生','李四','68'
  '李四他妈','医生','李四左同桌','70'
  '李四他爸','司机','李四左同桌','70'
  '李四他哥','程序猿','李四左同桌','70'
  '李四','大学学生','李四左同桌','70'
  '李四他妹','高中学生','李四左同桌','70'
  '李四他妈','医生','李四前排','90'
  '李四他爸','司机','李四前排','90'
  '李四他哥','程序猿','李四前排','90'
  '李四','大学学生','李四前排','90'
  '李四他妹','高中学生','李四前排','90'
  '李四他妈','医生','李四后排','53'
  '李四他爸','司机','李四后排','53'
  '李四他哥','程序猿','李四后排','53'
  '李四','大学学生','李四后排','53'
  '李四他妹','高中学生','李四后排','53'
  '李四他妈','医生','李四右同桌','100'
  '李四他爸','司机','李四右同桌','100'
  '李四他哥','程序猿','李四右同桌','100'
  '李四','大学学生','李四右同桌','100'
  '李四他妹','高中学生','李四右同桌','100'

  1. 小技巧

     ---

     1. 更新使用过滤条件中包含自身的表
        如：要求把同时存在于李四家庭表和李四班级表中的人的job字段更新为’无’。
        采用子查询，理论上可以写如下sql

     update table1 set job = '无' where table1.username in (
         select b.username from table1 a inner join table2 b on a.username = b.username
     );

     查询结果报错：

     Error Code: 1093. You can't specify target table 'table1' for update in FROM clause

     可以使用如下方式达到相同目的：

     update table1 a join (
         select b.username from table1 a join table2 b on a.username = b.username
     ) b on a.username = b.username set a.job = '无';

  2. 使用join优化子查询
     下面一个子查询

     select a.username, a.job, (select score from table2 b where a.username = b.username) as score from table1 a;

     使用join 优化

     select a.username, a.job, b.score as score from table1 a left join table2 b on a.username = b.username;

  3. 使用join来优化聚合函数子查询
     先，增加一个表，用来表示李四(userid = 1)，李四左同桌(userid = 2)，李前排(userid = 3)各个考试的分数

  id	userid	time	score
  1	1	‘2015-01-01’	65
  2	2	‘2015-01-01’	70
  3	3	‘2015-01-01’	85
  4	1	‘2015-05-01’	90
  5	2	‘2015-05-01’	71
  6	3	‘2015-05-01’	53
  7	1	‘2015-09-01’	69
  8	2	‘2015-09-01’	98
  9	3	‘2015-09-01’	86

  查询：查询出三个人考试分数最高的日期及分数
  使用下面查询可以满足条件

  select a.username, b.time, b.score from table2 a join table3 b on a.id = b.userid where b.score = (
  select max(c.score) from table3 c where c.userid = b.userid
  );

  查询结果

  '李四','2015-05-01','90'
  '李四左同桌','2015-09-01','98'
  '李四前排','2015-09-01','86'
  

  使用join优化后的查询：

   select a.username, b.time, b.score from table2 a 
   join table3 b on a.id = b.userid 
   join table3 c on c.userid = b.userid
   group by a.username, b.time, b.score
   having b.score = max(c.score);

  查询结果一样。

• 实现分组选择
  查询 李四(userid = 1)，李四左同桌(userid = 2)，李前排(userid = 3) 考试分数最高的两次考试的日期
  可使用下面查询：

select a.username, b.time, b.score from table2 a join table3 b on a.id = b.userid where a.username = '李四' order by b.score desc limit 2;
select a.username, b.time, b.score from table2 a join table3 b on a.id = b.userid where a.username = '李四左同桌' order by b.score desc limit 2;
select a.username, b.time, b.score from table2 a join table3 b on a.id = b.userid where a.username = '李四前排' order by b.score desc limit 2;

这样查询过于繁琐，如果分类很多，需要多次查询，这样增加了与数据库的交互次数，也增加了查询次数和网络流量，显然是不合理的。
使用下列查询达到相同的目的：

 select d.username, c.time, c.score from (
    select userid, time, score, (select count(*) from table3 b where b.userid = a.userid and a.score <= b.score) as cnt from table3 a group by userid, time, score
 ) c join table2 d on c.userid = d.id where cnt <=2;