项目开发中由于业务的需求,可能将两个业务实体放在两张表(或在两个查询结果)中,但有时候项目需要将这两张表(或查询结果)的所有记录联合起来,就可以用到UNION或UNION ALL,其中UNION ALL是不去重复,而UNION将把结果之和去重(类似于distinct)。
第一个查询:
SELECT cv.*
FROM study_card AS scd
LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id
LEFT JOIN course_view AS cv ON cv.id=sce.course_id
WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1
查询结果如图:
加上distinct后,
SELECT DISTINCT cv.*
FROM study_card AS scd
LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id
LEFT JOIN course_view AS cv ON cv.id=sce.course_id
WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1
查询结果如图:
第二个查询:
SELECT cv.*
FROM orders AS o
LEFT JOIN course_view AS cv ON o.course_id=cv.id
WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1
联合查询,用UNION ALL:
SELECT cv.*
FROM study_card AS scd
LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id
LEFT JOIN course_view AS cv ON cv.id=sce.course_id
WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1
UNION ALL
SELECT cv.*
FROM orders AS o
LEFT JOIN course_view AS cv ON o.course_id=cv.id
WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1
查询结果:
使用UNION:
SELECT cv.*
FROM study_card AS scd
LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id
LEFT JOIN course_view AS cv ON cv.id=sce.course_id
WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1
UNION
SELECT cv.*
FROM orders AS o
LEFT JOIN course_view AS cv ON o.course_id=cv.id
WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1
查询结果是:
使用联合查询的注意事项:
列数,列类型必须相同。
除此之外,联合查询可以加上分页LIMIT,如:
SELECT cv.*
FROM study_card AS scd
LEFT JOIN stuc_course AS sce ON scd.id=sce.stuc_id
LEFT JOIN course_view AS cv ON cv.id=sce.course_id
WHERE scd.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND scd.is_used=1
UNION
SELECT cv.*
FROM orders AS o
LEFT JOIN course_view AS cv ON o.course_id=cv.id
WHERE o.user_id='08fdeff8ca884d61a2c88c228f7d7404' AND o.is_finished=1
LIMIT 0,3