MySQL left join 与 sub query 子查询是否有区别

Question

以下2种是否存在区别？子查询与left join 是否存在区别

sqlselect a.*,b.* from a left join ( select * from sub) a on a.id=b.tid 
select a.*,b.* from a left join b on a.id=b.tid 

sqlexplain
SELECT `info_info`.*, `a`.`name` AS `d1name` 
FROM `info_info` 
    LEFT JOIN (SELECT * FROM `common_diqu`) `a` ON a.id=d1 WHERE `status`=1 
ORDER BY `tid` DESC


id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  info_info   ref     status  status  1   const   129947  Using where; Using filesort
1   SIMPLE  common_diqu     eq_ref  PRIMARY     PRIMARY     3   info_info.d1    1   Using where

explain 
select i.*,a.name as d1name
from info_info i 
left join common_diqu a on i.d1=a.id 
where status=1
order by tid desc 



id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  i   ref     status  status  1   const   129948  Using where; Using filesort
1   SIMPLE  a   eq_ref  PRIMARY     PRIMARY     3   i.d1    1   Using where

Answer 1

1.程序的逻辑不同。通常sub query比较符合程序的逻辑结构
2.运行的效率不同。通常认为join要高效些。
子查询的数量越多。效率越低。10倍以上时间差距是肯定的。而且这个还是数量不多情况的保守估计。如果类别过多。这个倍数就会越大。
3.一般新手更多的使用sub query因为要简单直观些。