子查询和join查询

join查询通常是代替子查询，典型的join查询是自连接，即自己和自己join起来查询数据。

练习一：

从下表中找出英语成绩=100的该同学的其他科目的成绩

方法一：子查询

方法二：自连接查询

练习二：

从下表中筛选出每个类别的销量前三的商品

方法一：

子查询

方法二：

自连接

关于上面自连接的逻辑解释：

fruit表自身左连接，会从笛卡尔积表中取出满足on条件的记录，并将左表中剩余部分也补足进去，这样的话，F.salecount<L_F.salecount,就会形成：最大的salecount的那个类别出现一次（这是左表中剩余部分补足进去的），第二大的salecount的那个类别在自身左连接过程中，出现一次，因为它只比最大的那个小，其次，第三大的那个salecount会出现两次，因此在group by 后的having中，对ID进行分组聚合后是count(id)<3可以得出各个分类中salecount排名前三的那些。

上面的解法有一个bug，如果水果类目里面，还有一个‘火龙果’价格也是866，那么销量第三高的樱桃的677就会选不出来，就得不到想要的每个类目的前三名。

问题出现在 having count()那里，因为将左表中的677与右表进行比较时，遇到右表的两个866，会返回两次677，这样就增加了677的count次数，遇到897时一次，遇到866时两次，这样就有了三次677，在进行having count（l.id）<3时就不会得到677。可以用右表来限制取值，对左表的l.id进行group by以后，与左表进行比较的右表中的比相应左表大的值也会划分进左表的group by里面，这时取having(distinct L_F.salecount)<3,逻辑是右表中比左表相应数据大的数据的distinct值小于3个，那么左表的值就排前三，比如，最表是677，右表是相应的866，866，897，distinct后是两个数值比左表大，那么左表的这个值就排行老三，就被取出来了，左表的866和897是同样的道理。

练习三：

person表：

movie表：

方法二：连接查询

子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：

semijoin

Materialization

exists

2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：

Materialization

exists

3. 对于 derived 派生表，优化器有如下策略选择：

derived_merge，将派生表合并到外部查询中（5.7 引入 ）；

将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

一般来讲，关系数据库中需要用指定连接方式(例如指定内连接、左右连接、全外连接)来组合、筛选来自多张表(包括1张表自连接)或查询或视图的信息时就要使用join连接。

不需要连接、或者需要连接多表，但是不指定连接方式的连接(如在where 子句里规定连接条件)、子查询(exists子查询、in子查询等)就不用join。

---