连接查询带where条件（学到的新知识）

描述一下遇到的问题
表1：snId，type1
表2：snId，type2，result，
目的：根据snId做关联，满足表1的type1条件和表2的type2条件将结果查到一个表内！

sql优化之旅！（单查询层面）

        select
        batch_id, equipment_info.sn_id, unit_type, unit_home, intrinsic_calibration_state,
        intrinsic_check_result,intrinsic_use_state,calibration_info.calibration_result
        from
        equipment_info
        left join
        calibration_info
        on
        equipment_info.sn_id=calibration_info.sn_id        
		where equipment_type=1 and calibration_type=1

乍一看，sql读起来很符合我们的要求，但是结果呢？

修改一下

        select
        batch_id, newTable1.sn_id, unit_type, unit_home, intrinsic_calibration_state,
        intrinsic_check_result,intrinsic_use_state,newTable2.calibration_result
        from
        ( select
        batch_id, sn_id, unit_type, unit_home, intrinsic_calibration_state,
        intrinsic_check_result,intrinsic_use_state
        from
        equipment_info
        where equipment_type=1) as newTable1
        left join
        (select sn_id,calibration_type,calibration_result
        from
        calibration_info
        where calibration_type=1) as newTable2
        on
        newTable1.sn_id=newTable2.sn_id

这sql看起来好烦人，查了好几遍但是结果没毛病

优化一下

        select
        batch_id, equipment_info.sn_id, unit_type, unit_home, intrinsic_calibration_state,
        intrinsic_check_result,intrinsic_use_state,equipment_type,calibration_result
        from
        equipment_info
        left join
       (select sn_id,calibration_result
        from
        calibration_info
        where calibration_type=1) as newTable
        on
        equipment_info.sn_id=newTable.sn_id
		WHERE equipment_type=1 

好像结果也没啥问题

最后，请教大佬

        select
        batch_id, equipment_info.sn_id, unit_type, unit_home, intrinsic_calibration_state,
        intrinsic_check_result,intrinsic_use_state,calibration_result
        from
        equipment_info
        left join
        calibration_info
        on
        equipment_info.sn_id=calibration_info.sn_id and calibration_type=1
		where equipment_type=1

这个and学到了（需要看一下，sql执行顺序）
from -> join -> on -> where -> group by ->having -> select-> distinct -> order by-> limit