【MySQL】mysql-left join陷阱

最新推荐文章于 2021-01-27 16:05:44 发布

转载最新推荐文章于 2021-01-27 16:05:44 发布 · 135 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/flydkPocketMagic/p/MySQL.html

文章标签：

#数据库

.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 = =。

转载于:https://www.cnblogs.com/flydkPocketMagic/p/MySQL.html