【MySQL】mysql-left join陷阱

.where和and的差异

SELECT
    ota_distributor_product_relation.ota_id,
    ota_distributor_product_relation.ota_name,
    ota_distributor_product_relation.sale_status,
    ota_distributor_product_relation.id,
    ota_stock_price.guide_price,
    ota_stock_price.purchase_price,
    ota_stock_price.scenic_price,
  ota_stock_price.stock
FROM
    ota_distributor_product_relation
LEFT JOIN ota_stock_price ON ota_distributor_product_relation.id = ota_stock_price.relation_id
AND ota_distributor_product_relation.ticket_id='06d02f2d583746f2ad7ac4a0407f3499' AND ota_stock_price.sp_date='2017-06-29'

 

SELECT
    ota_distributor_product_relation.ota_id,
    ota_distributor_product_relation.ota_name,
    ota_distributor_product_relation.sale_status,
    ota_distributor_product_relation.id,
    ota_stock_price.guide_price,
    ota_stock_price.purchase_price,
    ota_stock_price.scenic_price,
  ota_stock_price.stock
FROM
    ota_distributor_product_relation
LEFT JOIN ota_stock_price ON ota_distributor_product_relation.id = ota_stock_price.relation_id
where ota_distributor_product_relation.ticket_id='06d02f2d583746f2ad7ac4a0407f3499' AND ota_stock_price.sp_date='2017-06-29'

 

可以看到两种查询得到的结果不一样，具体原因可以查看这篇博文：

http://www.oschina.net/question/89964_65912

以前没注意，不知道是不是写了很多bug = =。