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

开发调优
1

两个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有本质区别）