表连接查询

内连接
inner join

explain 
select count(*) from 	  BASE base  
inner join A a on base.id = a.a_id  
inner join B b on base.id = b.b_id 

结果等同于

explain
select  count(*) from 	  
BASE base  
,A a  
,B b
WHERE  base.id = a.project_id and  base.id = b.b_id 

需要满足每一个条件，数据量最小

左连接
left join

explain 
select count(*) from 	  BASE base  
left join A a on base.id = a.a_id  
left join B b on base.id = b.b_id 

以左边的表为主表进行数据组装，数据值跟

select count(*) from 	  BASE base  

相等

右连接

right join

explain 
select count(*) from 	  BASE base  
right join A a on base.id = a.a_id  
right join B b on base.id = b.b_id

以右边的表为主表进行数据组装

记录个工作中用到的sql ，分库分表的数据没有进行聚合，生产要进行业务对数据，没办法只能连表挨个筛选，一辈子再也不想干这种事，记录下

select count(*) from (

(select c_no,user from  c_info_0000 WHERE m_code='0001' AND status=3 ) 
union 
(select c_no,user from  c_info_0001 WHERE m_code='0001' AND status=3 ) 
union 
(select c_no,user from  c_info_0002 WHERE m_code='0001' AND status=3 ) 
) x

select update_time, count(*) as afrom (

(select c_no,user,date_format(update_time,'%Y-%m-%d') as update_time from  c_info_0000 WHERE m_code='0001' AND status=3 ) 
union 
(select c_no,user,date_format(update_time,'%Y-%m-%d') as update_time from  c_info_0001 WHERE m_code='0001' AND status=3 ) 
union 
(select c_no,user,date_format(update_time,'%Y-%m-%d') as update_time from  c_info_0002 WHERE m_code='0001' AND status=3 ) 
) x  group by update_time