union 与 union all 区别
union all 展示所有结果
union 展示去重后的结果
- union all
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE
sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('0', '3', '4')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
UNION ALL
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM un_StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('1', '2')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
Explain:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filterd | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | sc | ref | IntelUserCode | IntelUserCode | 108 | const | 1 | 5 | Using index condition; Using where; Using filesort | |
| 1 | PRIMARY | ua | ref | IntelUserCode | IntelUserCode | 108 | const | 2 | 10 | Using where; Using index | |
| 2 | UNION | ua | ref | IntelUserCode | IntelUserCode | 108 | const | 2 | 10 | Using where; Using index; Using temporary; Using filesort | |
| 2 | UNION | sc | ref | IntelUserCode | IntelUserCode | 108 | const | 1 | 100 | Using index condition; Using where |
- union
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE
sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('0', '3', '4')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
UNION
(SELECT
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status,
bizname(sc.DepartmentOfDuty) AS DepartmentOfDutyName,
bizname(sc.Duty) AS Duty,
bizname(sc.DutyStatus) AS DutyStatus,
group_concat(ua.AttachmentCode) AS AttachmentCode
FROM un_StudentCadres sc
JOIN userattachment ua ON sc.IntelUserCode = ua.IntelUserCode AND ua.BizType = 'StudCadres'
WHERE sc.Status = '1' AND sc.IntelUserCode = '7b9374f5-d21d-416e-bc1b-f632cd6542ee' AND ApproveStatus IN ('1', '2')
GROUP BY
sc.IntelUserCode,
sc.StartDate,
sc.EndDate,
sc.DepartmentOfDuty,
sc.Duty,
sc.DutyStatus,
sc.BizType,
sc.TaskCode,
sc.ApproveStatus,
sc.Operator,
sc.Status)
Explain:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filterd | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | sc | ref | IntelUserCode | IntelUserCode | 108 | const | 1 | 5 | Using index condition; Using where; Using filesort | |
| 1 | PRIMARY | ua | ref | IntelUserCode | IntelUserCode | 108 | const | 2 | 10 | Using where; Using index | |
| 2 | UNION | ua | ref | IntelUserCode | IntelUserCode | 108 | const | 2 | 10 | Using where; Using index; Using temporary; Using filesort | |
| 2 | UNION | sc | ref | IntelUserCode | IntelUserCode | 108 | const | 1 | 100 | Using index condition; Using where | |
| UNION RESULT | <union1,2> | ALL | Using temporary |
union 比 union all 多用了一个临时表存储去重后的数据
