left join on and 与 left join on where的区别

left join on and where
左连接，pb_house表数据要比关联表数据多。
先过滤pb_community 、pb_xiaoqu 、pb_building表中IS_DELETE = 0的数据得到一张临时表A（表A的数据应该和pb_house表数据一样多）然后对这个临时表A作WHERE条件过滤，这里的条件只有一个，过滤之后的数据自然比left join on where and这种方式多。

SELECT
    ph.*, pc.COMM_NAME,
    px.XIAOQU_NAME,
    pbl.BUILD_NAME
FROM
    pb_house ph
LEFT JOIN pb_community pc ON ph.COMMUNITY_ID = pc.COMMUNITY_ID
AND pc.IS_DELETE = 0
LEFT JOIN pb_xiaoqu px ON ph.XIAOQU_ID = px.XIAOQU_ID
AND px.IS_DELETE = 0
LEFT JOIN pb_building pbl ON ph.BUILD_ID = pbl.BUILD_ID
AND pbl.IS_DELETE = 0
WHERE
    ph.IS_DELETE = 0

等同于以下，视图写法

SELECT
    `ph`.`HOUSE_ID` AS `HOUSE_ID`,
    `ph`.`HOUSE_NO` AS `HOUSE_NO`,
    `ph`.`RESIDENT_ID` AS `RESIDENT_ID`,
    `ph`.`OWNER_NAME` AS `OWNER_NAME`,
    `ph`.`COMM_RIGHT` AS `COMM_RIGHT`,
    `ph`.`HOUSE_ADDR` AS `HOUSE_ADDR`,
    `ph`.`HOUSE_NAME` AS `HOUSE_NAME`,
    `ph`.`HOUSE_AREA` AS `HOUSE_AREA`,
    `ph`.`FLOOR` AS `FLOOR`,
    `ph`.`LAYOUT_STRUCTURE_ID` AS `LAYOUT_STRUCTURE_ID`,
    `ph`.`HOUSE_PROPERTY_TYPE_ID` AS `HOUSE_PROPERTY_TYPE_ID`,
    `ph`.`HOUSE_PROPERTY_CARD` AS `HOUSE_PROPERTY_CARD`,
    `ph`.`HOUSE_STAT` AS `HOUSE_STAT`,
    `ph`.`HOUSE_USE` AS `HOUSE_USE`,
    `ph`.`HOUSE_TYPE` AS `HOUSE_TYPE`,
    `ph`.`HOUSE_NATURE` AS `HOUSE_NATURE`,
    `ph`.`IS_DELETE` AS `IS_DELETE`,
    `ph`.`DATA_SOURCE_ID` AS `DATA_SOURCE_ID`,
    `ph`.`BUILD_ID` AS `BUILD_ID`,
    `ph`.`XIAOQU_ID` AS `XIAOQU_ID`,
    `ph`.`COMMUNITY_ID` AS `COMMUNITY_ID`,
    `ph`.`ADM_DISTRICT_ID` AS `ADM_DISTRICT_ID`,
    `ph`.`ADM_STREET_ID` AS `ADM_STREET_ID`,
    `ph`.`COMM_GIS_ID` AS `COMM_GIS_ID`,
    `ph`.`SRC_UPDATE_TIME` AS `SRC_UPDATE_TIME`,
    `ph`.`FG_UPDATE_TIME` AS `FG_UPDATE_TIME`,
    `ph`.`UPDATE_TIME` AS `UPDATE_TIME`,
    `ph`.`ISBUILDING` AS `ISBUILDING`,
    `pc`.`COMM_NAME` AS `COMM_NAME`,
    `px`.`XIAOQU_NAME` AS `XIAOQU_NAME`,
    `pbl`.`BUILD_NAME` AS `BUILD_NAME`
FROM
    (
        (
            (
                `pb_house` `ph`
                LEFT JOIN `pb_community` `pc` ON (
                    (
                        (
                            `ph`.`COMMUNITY_ID` = `pc`.`COMMUNITY_ID`
                        )
                        AND (`pc`.`IS_DELETE` = 0)
                    )
                )
            )
            LEFT JOIN `pb_xiaoqu` `px` ON (
                (
                    (
                        `ph`.`XIAOQU_ID` = `px`.`XIAOQU_ID`
                    )
                    AND (`px`.`IS_DELETE` = 0)
                )
            )
        )
        LEFT JOIN `pb_building` `pbl` ON (
            (
                (
                    `ph`.`BUILD_ID` = `pbl`.`BUILD_ID`
                )
                AND (`pbl`.`IS_DELETE` = 0)
            )
        )
    )
WHERE
    (`ph`.`IS_DELETE` = 0)

left join on where and
这种方式先关联三张表得到一张临时表A（表A的数据应该和pb_house表数据一样多），然后对这个临时表A作WHERE条件过滤，因为这里不止一个过滤条件，需要同时满足这几个条件的数据才会出现，所以过滤之后的数据自然比left join on and where这种方式少。

SELECT
    ph.*, pc.COMM_NAME,
    px.XIAOQU_NAME,
    pbl.BUILD_NAME
FROM
    pb_house ph
LEFT JOIN pb_community pc ON ph.COMMUNITY_ID = pc.COMMUNITY_ID
LEFT JOIN pb_xiaoqu px ON ph.XIAOQU_ID = px.XIAOQU_ID
LEFT JOIN pb_building pbl ON ph.BUILD_ID = pbl.BUILD_ID
WHERE
    ph.IS_DELETE = 0
AND pc.IS_DELETE = 0
AND px.IS_DELETE = 0
AND pbl.IS_DELETE = 0

等同于视图

SELECT
    `ph`.`HOUSE_ID` AS `HOUSE_ID`,
    `ph`.`HOUSE_NO` AS `HOUSE_NO`,
    `ph`.`RESIDENT_ID` AS `RESIDENT_ID`,
    `ph`.`OWNER_NAME` AS `OWNER_NAME`,
    `ph`.`COMM_RIGHT` AS `COMM_RIGHT`,
    `ph`.`HOUSE_ADDR` AS `HOUSE_ADDR`,
    `ph`.`HOUSE_NAME` AS `HOUSE_NAME`,
    `ph`.`HOUSE_AREA` AS `HOUSE_AREA`,
    `ph`.`FLOOR` AS `FLOOR`,
    `ph`.`LAYOUT_STRUCTURE_ID` AS `LAYOUT_STRUCTURE_ID`,
    `ph`.`HOUSE_PROPERTY_TYPE_ID` AS `HOUSE_PROPERTY_TYPE_ID`,
    `ph`.`HOUSE_PROPERTY_CARD` AS `HOUSE_PROPERTY_CARD`,
    `ph`.`HOUSE_STAT` AS `HOUSE_STAT`,
    `ph`.`HOUSE_USE` AS `HOUSE_USE`,
    `ph`.`HOUSE_TYPE` AS `HOUSE_TYPE`,
    `ph`.`HOUSE_NATURE` AS `HOUSE_NATURE`,
    `ph`.`IS_DELETE` AS `IS_DELETE`,
    `ph`.`DATA_SOURCE_ID` AS `DATA_SOURCE_ID`,
    `ph`.`BUILD_ID` AS `BUILD_ID`,
    `ph`.`XIAOQU_ID` AS `XIAOQU_ID`,
    `ph`.`COMMUNITY_ID` AS `COMMUNITY_ID`,
    `ph`.`ADM_DISTRICT_ID` AS `ADM_DISTRICT_ID`,
    `ph`.`ADM_STREET_ID` AS `ADM_STREET_ID`,
    `ph`.`COMM_GIS_ID` AS `COMM_GIS_ID`,
    `ph`.`SRC_UPDATE_TIME` AS `SRC_UPDATE_TIME`,
    `ph`.`FG_UPDATE_TIME` AS `FG_UPDATE_TIME`,
    `ph`.`UPDATE_TIME` AS `UPDATE_TIME`,
    `ph`.`ISBUILDING` AS `ISBUILDING`,
    `pc`.`COMM_NAME` AS `COMM_NAME`,
    `px`.`XIAOQU_NAME` AS `XIAOQU_NAME`,
    `pbl`.`BUILD_NAME` AS `BUILD_NAME`
FROM
    (
        (
            (
                `pb_house` `ph`
                LEFT JOIN `pb_community` `pc` ON (
                    (
                        `ph`.`COMMUNITY_ID` = `pc`.`COMMUNITY_ID`
                    )
                )
            )
            LEFT JOIN `pb_xiaoqu` `px` ON (
                (
                    `ph`.`XIAOQU_ID` = `px`.`XIAOQU_ID`
                )
            )
        )
        LEFT JOIN `pb_building` `pbl` ON (
            (
                `ph`.`BUILD_ID` = `pbl`.`BUILD_ID`
            )
        )
    )
WHERE
    (
        (`ph`.`IS_DELETE` = 0)
        AND (`pc`.`IS_DELETE` = 0)
        AND (`px`.`IS_DELETE` = 0)
        AND (`pbl`.`IS_DELETE` = 0)
    )

两种方式的过程如下

微信图片_20181207104429_副本.jpg

有person表和student表，当person表→ student表是1对多的关系时，左连接数据变多。

person表.png student表.png

SELECT
    *
FROM
    person
LEFT JOIN student ON person.id = student.person_id

image.png

还是这两张表，当多对1的时候（多不是指数据多，而是对应关系）数据不会变多。

SELECT
    *
FROM
    student
LEFT JOIN person ON person.id = student.person_id

image.png