mysql基础---->join的使用

　　这里记录一下mysql关于查询中join的一些用法。不是因为看到希望了才去坚持，而是坚持了才知道没有希望。

mysql中的join用法

准备的测试数据库脚本如下：

CREATE TABLE t1 (id INT, type1 char(1));
insert into t1 values(1, 'a');
insert into t1 values(2, 'b');
insert into t1 values(3, 'c');

CREATE TABLE t2 (id INT, type2 char(1));
insert into t2 values(2, 'x');
insert into t2 values(3, 'y');
insert into t2 values(4, 'z');

一、inner join的使用

在mysql中，JOIN, CROSS JOIN, and INNER JOIN语法是等价，它们可以相互替换。但是在标准的sql中，它们不是等价的。

select * from t1 inner join t2;

select * from t1 inner join t2 on t1.id = t2.id order by t1.id;

select * from t1 inner join t2 using(id);

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "t1" 中的行在 "t2" 中没有匹配，就不会列出这些行。注意如果颠倒语句中的t1和t2表的位置并不会影响返回行的结果,只会影响字段显示的顺序。

二、left join的使用

select * from t1 left join t2 on t1.id = t2.id order by t1.id;

A USING clause can be rewritten as an ON clause that compares corresponding columns. However, although USING and ON are similar, they are not quite the same. Consider the following two queries:

select * from t1 left join t2 using(id) order by t1.id;

可以发现这里有些行的字段值是空的那是因为 LEFT JOIN 关键字会从左表 (t1) 那里返回所有的行，即使在右表 (t2) 中没有匹配的行。也就是说在没有加上where+条件的情况下左表中的数据将会被全部列出，并连接了右表的数据，如果没有匹配，右表的字段值就为空。

三、right join的使用

select * from t1 right join t2 on t1.id = t2.id order by t1.id;

RIGHT JOIN 关键字会从右表 (t2) 那里返回所有的行，即使在左表 (t1) 中没有匹配的行。

四、join的在项目中使用案例一

　　需求：我们要查出所有的ACTIVITYSCENE数据，但是会联表Dept查询出对应的机构名称。如果机构名称没有，那么显示null。个人感觉left join在这种联表查询中，要查询其它表中的一些数据，而这些表为空或者字段为空。这一块使用left join比较方便。我们可以不使用java代码去处理它的逻辑。

SELECT
    activitySeq, sceneSeq, beginDate, endDate, location, ifSelectDept, a.deptSeq, deptname as deptName
FROM
    ACTIVITYSCENE a
LEFT JOIN
    DEPT b
ON
    a.deptSeq = b.deptSeq

运行的结果如下：

如果是平常的sql语句：

SELECT
    activitySeq, sceneSeq, beginDate, endDate, location, ifSelectDept, a.deptSeq, deptname as deptName
FROM
    ACTIVITYSCENE a, DEPT b
WHERE
    a.deptSeq = b.deptSeq

此时运行的结果如下：

对ACTIVITYSCENE进行where过滤：

SELECT
    activitySeq, sceneSeq, beginDate, endDate, location, ifSelectDept, a.deptSeq, deptname as deptName
FROM 
    ACTIVITYSCENE a 
LEFT JOIN
    DEPT b
ON
    a.deptSeq = b.deptSeq
WHERE
    1 = 1 
<if test="activitySeq != null">
    AND a.activitySeq = #{activitySeq}
</if>
<if test="location != null">
    AND a.location like "%"#{location}"%"
</if>
ORDER BY
    a.beginDate DESC

传入activitySeq=321，查询出来的结果如下：

五、join的在项目中使用案例二

　　前端展示所有的活动，该活动要显示用户的收藏和关注信息。涉及到3张表，活动表(ACTIVITY )、报名表(USERACTIVITY)、收藏表(ACTIVITYATTENTION)。需要说明的是：用户没有关注时，报名表可能没有对应的数据或者是相应的关注字段为0(同理收藏)。

java的处理代码如下：

Map handleInner(HttpServletRequest request, HttpServletResponse response) throws Exception {
    Map map = new HashMap<>();
    map.put("actType", Constants.ACTIVITY_TYPE_COMMUNITE); // 主题活动
    map.put("deptSeq", request.getAttribute("deptSeq"));
    map.put("openId", "ohLPgwMB-xSggJsziXnphlqEG0xY");
    map.put("actEndDate", new Date());
　　map.put("userSeq", null);
    Cif puser = (Cif)request.getSession().getAttribute("_PUser");
    if (puser != null) {
        map.put("userSeq", puser.getUserseq());
    }
    List<Map> realList = activityService.queryCommuniteSignAndAttentionList(map);
    Map result = new HashMap();
    result.put(TransField.RETURNCODE, CheckMsg.SUCCESS);
    result.put("List", realList);
    return result;
}

mybatis的xml脚本如下：

<select id="queryCommuniteSignAndAttentionList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
    SELECT 
        a.activitySeq, a.actType, actTitle, actCreatetime, deptSeq, actMerchant, actBeginDate, actEndDate, attentionStatus, isSignIn
    FROM 
        ACTIVITY a 
    LEFT JOIN
        (USERACTIVITY b, ACTIVITYATTENTION c)
    ON
        a.activitySeq = b.activitySeq AND
        a.activitySeq = c.activityId
    <if test="userSeq != null">
        AND b.userSeq = #{userSeq}
    </if>
    <if test="openId != null">
        AND c.openId = #{openId}
    </if>
    WHERE
        1 = 1
    <if test="deptSeq != null">
        AND a.deptSeq = #{deptSeq}
    </if>
    <if test="actType != null">
        AND a.actType = #{actType}
    </if>
    <if test="actEndDate != null">
        AND a.actEndDate &gt;= CURDATE()
    </if>
    ORDER BY
        actCreatetime DESC
</select>

一次的运行数据如下：

上述的where的条件语句是对最终数据的过滤，不能写在ON里面。这里其实有一些问题，userSeq只与USERACTIVITY表关联，openId与ACTIVITYATTENTION表关联。上述的写在一个left join的on后面是不合理的。查询出来的数据是有问题的。正确的做法如下：

<!-- 查询活动的列表 -->
<select id="queryCommuniteSignAndAttentionList" resultType="java.util.HashMap" parameterType="java.util.HashMap">
    SELECT 
        a.activitySeq, a.actType, actTitle, actCreatetime, deptSeq, actMerchant, actBeginDate, actEndDate, attentionStatus,isSignUp, a.actLocation actLocation
    FROM 
        ACTIVITY a 
    LEFT JOIN
        USERACTIVITY b 
    ON
        a.activitySeq = b.activitySeq AND 
        b.userSeq = #{userSeq}
    LEFT JOIN 
        ACTIVITYATTENTION c
    ON
        a.activitySeq = c.activityId AND 
        c.openId = #{openId}
    WHERE
        1 = 1
    <if test="deptSeq != null">
        AND a.deptSeq = #{deptSeq}
    </if>
    <if test="actType != null">
        AND a.actType = #{actType}
    </if>
    <if test="actEndDate != null">
        AND a.actEndDate &gt;= CURDATE()
    </if>
    ORDER BY
        actCreatetime DESC
</select>

一次的生成的sql语句如下：select里面的字段只写出部分

SELECT
    a.activitySeq, a.actType, actTitle, actCreatetime, deptSeq, actMerchant, actBeginDate, actEndDate, attentionStatus, isSignUp
FROM
    ACTIVITY a
        LEFT JOIN
    USERACTIVITY b ON a.activitySeq = b.activitySeq
        AND b.userSeq = 161
        LEFT JOIN
    ACTIVITYATTENTION c ON a.activitySeq = c.activityId
        AND c.openId = 'o2UcpwKxxSM2WHn5xPKY5Zt7xX5k'
WHERE
    1 = 1 AND a.actType = 1
        AND a.actEndDate >= CURDATE()
ORDER BY actCreatetime DESC

运行的结果如下：

六、join的在项目中使用案例三

　　查询用户的收藏活动，其中还要查询这些收藏活动的报名情况，表的结构和上述一样。注意from后面的括号是需要的。之前的userSeq和openId是判断式的，这个会导致没有注册或者没有关注的用户，查询出来的列表会有重复的数据。去掉的判断，可以解决这个bug。

<select id="queryMyAttentionActivity" resultType="java.util.HashMap" parameterType="java.util.HashMap">
    SELECT 
        actLogo, actCreatetime, actMerchant, deptSeq, actBeginDate, actEndDate, attentionStatus, isSignIn
    FROM 
        (ACTIVITYATTENTION a, ACTIVITY b)
    LEFT JOIN
        USERACTIVITY c 
    ON
        a.activityId = c.activitySeq
        AND c.userSeq = #{userSeq}
    WHERE
        a.activityId = b.activitySeq
        AND a.openId = #{openId}
    <if test="attentionStatus != null">
        AND a.attentionStatus = #{attentionStatus}
    </if>
    ORDER BY
        actCreatetime DESC
</select>

一次的运行结果如下：