mysq联表查询

select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;
select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;
select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId 
union 
select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

select *
  from tbl_dept a
  right join tbl_emp b on a.id= b.deptId
 where a.id is null
 union
select *
  from tbl_dept a
  left join tbl_emp b on a.id= b.deptId
 where b.deptId is null;

连子表

SELECT  a.total1 as total,a.UID from
(SELECT SUM(`amount`) as total1, `to_userid` as UID
  FROM `t_diamond_cost_log`
 WHERE `user_id`= 1640641
   and `type`= 1
   and `to_userid`<> 1
   and `cdate`> "2019-07-16 00:00:00"
 GROUP BY `to_userid`) as a
left join
(SELECT SUM(`amount`) as total2, `user_id` as UID
  FROM `t_diamond_cost_log`
 WHERE `to_userid`= 1640641
   and `type`= 1
   and `user_id`<> 1
   and `cdate`> "2019-07-16 00:00:00"
 GROUP BY `user_id`) as b
 ON a.UID= b.UID