首先,要分析它们的区别:UNION ALL和UNION
1.取结果的交集,union对两个结果集进行并集操作,不包括重复行,相当于distinct,同时进行默认规则的排序;
2.union all:对两个结果集进行并集操作,包括重复行,即所有的结果全部显示,不管是不是重复。
UNION用的比较多union all是直接连接,取到得是所有值,记录可能有重复 union 是取唯一值,记录没有重复
SELECT
cg.id,
ce.username AS username,
ce.gender,
ce.mobile,
ce.email,
cg.`name`
FROM
contacts_employee ce
JOIN contacts_group_emp cge ON ce.id = cge.emp_id
JOIN contacts_group cg ON cge.group_id = cg.id
UNION ALL
SELECT
cg.id,
ceo.employee_name AS username,
ceo.gender,
ceo.mobile,
ceo.email,
cg.`name`
FROM
contacts_employee_outside ceo
JOIN contacts_group_empout cgo ON ceo.id = cgo.empout_id
JOIN contacts_group cg ON cg.id = cgo.group_id
5表联查数据过于混乱和重复,故使用了UNION ALL和UNION