MySQL 连接查询时 where 和 on 的区别

21-05-12 23:24 字数 1308 阅读 1443 已编辑

两个SQL,第一个能查询出正常结果,第二个不能。

正确的SQL

SELECT
    tmp.*,
    cpr.id,
    cpr.repeat_times
FROM
    ( SELECT MIN( remind_time ) AS remind_time, pet_id, type FROM cp_pet_remind WHERE user_id = 1 AND `status` = 1 GROUP BY pet_id, type ) AS tmp
    LEFT JOIN cp_pet_remind AS cpr ON tmp.remind_time = cpr.remind_time 
    AND tmp.type = cpr.type 
    AND tmp.pet_id = cpr.pet_id
    WHERE (cpr.repeat_times = 1 AND (UNIX_TIMESTAMP(tmp.remind_time) > 1620832712)) OR cpr.repeat_times > 1;

错误的SQL

SELECT
    tmp.*,
    cpr.id,
    cpr.repeat_times
FROM
    ( SELECT MIN( remind_time ) AS remind_time, pet_id, type FROM cp_pet_remind WHERE user_id = 1 AND `status` = 1 GROUP BY pet_id, type ) AS tmp
    LEFT JOIN cp_pet_remind AS cpr ON tmp.remind_time = cpr.remind_time 
    AND tmp.type = cpr.type 
    AND tmp.pet_id = cpr.pet_id
    AND (cpr.repeat_times = 1 AND (UNIX_TIMESTAMP(tmp.remind_time) > 1620832712)) OR cpr.repeat_times > 1;

结论

在使用left join时,on and和on where条件的区别如下:

  • on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
  • 左连接中如果and语句是对左表进行过滤的,那么不管真假都不起任何作用。如果是对右表过滤的,那么左表所有记录都返回,右表筛选以后再与左表连接返回
  • where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

在使用inner join时,on and和on where条件区别如下:

  • on and 或者on where不管是对左表还是右表进行过滤,实际都是在生成临时表以后再进行过滤的,而且对左表和右表都起作用(故反映left join和inner join有本质区别)
0人点赞>
关注 收藏 改进 举报
0 条评论
排序方式 时间 投票
快来抢占一楼吧
请登录后发表评论
站长 @ 十七度
文章
383
粉丝
23
喜欢
195
收藏
31
排名 : 1
访问 : 136.1万
私信