mySql联合两个查询结果用UNION或者UNION ALL

项目开发中由于业务的需求，可能将两个业务实体放在两张表（或在两个查询结果）中，但有时候项目需要将这两张表（或查询结果）的所有记录联合起来，就可以用到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