SQL（LEFT JOIN）

一、left join

以左表为主表，右表匹配不上补null。

1.有on的情况

select * 
from t_name a
left join
t_age b 
on a.id = b.id


SELECT DISTINCT
	a.person_id AS '工号',
	a.`name` AS '名字',
	c.grade_score AS '考试总得分',
	b.department_name AS '用户二级部门',
	f.department_name AS '试卷二级部门',
	g.title AS '试卷名',
	g.total_score AS '试卷总分',
	g.pass_score AS '试卷及格分',
	c.choice_score AS '单选总分得',
	c.selection_score AS '多选总得分',
	c.judge_score AS '判断总得分',
	c.updated_time AS '考试时间' 
FROM
	( t_account a LEFT JOIN t_department b ON a.`name` = b.creator OR a.second_department_id = b.id )
	LEFT JOIN t_grade c ON a.id = c.account_id
	LEFT JOIN t_paper d ON d.second_department_id = b.id
	LEFT JOIN t_department f ON f.id = d.second_department_id
	LEFT JOIN t_paper g ON g.id = c.paper_id

此时以左表为准，左表数据一定完整。

2.有where的情况

select *
from t_name a
left join
t_age b
on a.id = b.id
where b.age = 24

此时是对(1)中的数据进行筛选过滤，因此左表的数据不一定是完整的。