in 和 join 的区别

SELECT
    address,
    planTime,
    temperature
FROM
    rcc_d_temperaturePanelHistoricalData_view
WHERE
    (houseId, planTime) IN (
        SELECT
            houseId,
            max(planTime) planTime
        FROM
            rcc_d_temperaturePanelHistoricalData_view
        WHERE
            factoryId = 11
        AND stationId = 3
        AND planTime BETWEEN '2020-04-03 10:00:00'
        AND '2020-04-19 11:00:00'
        GROUP BY
            houseId,
            DATE_FORMAT(planTime, '%Y-%m-%d %H')
    )
AND factoryId = 11
AND stationId = 3
AND planTime BETWEEN '2020-04-03 10:00:00'
AND '2020-04-19 11:00:00'

 

SELECT
    address,
    houseHoldName,
    temperature,
    chargeState,
    heatingType,
    factory
FROM
    rcc_d_temperaturePanelHistoricalData_view t1
RIGHT JOIN (
    SELECT
        houseId maxId,
        max(planTime) maxTime
    FROM
        rcc_d_temperaturePanelHistoricalData_view
    WHERE
        factoryId = 11
    AND stationId = 3
    AND planTime BETWEEN '2020-04-03 10:00:00'
    AND '2020-04-19 11:00:00'
    GROUP BY
        houseId,
        DATE_FORMAT(planTime, '%Y-%m-%d %H')
) t2 ON t1.houseId = t2.maxId AND t1.planTime = t2.maxTime

 

 子查询的结果可以使用 in 也可以使用 right join，目前看两者的效率是差不多的，但是查资料说当数据量越大的时候 join 的方式会比 in 的方式快的越多，

其次 in 的后面一定要加其他查询条件，而 join 的后面可以省略重复的查询条件，in 后面的查询条件如果省略掉的话，结果虽然一致，但是会慢上将近百倍，0.0几秒的查询会变成几秒