SQL基础知识：join操作后面的on与where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用1eft jion时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
假设有两张表：

两条 SQL：

selectform tabl left join tab2 on（tabl.size=tab2.size）where tab2.name='AAA'


selectform tabl left join tab2 on（tabl.size=tab2.size and tab2.name='AAA）

第一条SQL的过程：
1、中间表on条件；

2、再对中间表过滤where条件：tab2.name=AAA’tab1.id tab1.size tab2.size tab2.name

第二条SQL的过程：
1、中间表on条件：tabl.size=tab2.size and tab2.name=’AAA’（条
件不为真也会返回左表中的记录）

===============================================

示例二：

group查询，不增加指定用户限定 dict.gmt_creator = ‘32255’

SELECT
	region.region_id,
	region.region_name,
	region.gmt_creator AS table1_gmt_creator,
	table2_gmt_creator 
FROM
	(
	SELECT
		agg.*,
		dict.type AS type,
		dict.gmt_creator AS table2_gmt_creator 
	FROM
		monitor_region_agg agg
		LEFT JOIN monitor_dict dict ON agg.region_id = dict.region_id 
		AND agg.is_delete = '0' 
		AND dict.type IN ( 'attention', 'readStatus' ) 
	WHERE
		1 = 1 
		AND agg.tenant_code = '00000007' 
		AND agg.business_code = '005' 
		AND agg.app_code = '005' 
	) region
	LEFT JOIN monitor_region_person_relation person ON region.region_id = person.region_id 
WHERE
	region.`is_delete` = '0' 
	AND person.`is_delete` = '0' 
	AND person.gather_flag = '0' 
	AND region.tenant_code = '00000007' 
	AND region.business_code = '005' 
	AND region.app_code = '005' 
	AND region.risk_date >= '2020-10-20' 
	AND region.risk_date <= '2021-01-20' 
GROUP BY
	region.region_id

结果：

二、group查询，增加指定用户限定 dict.gmt_creator = ‘32255’

SELECT
	region.region_id,
	region.region_name,
	region.gmt_creator AS table1_gmt_creator,
	table2_gmt_creator 
FROM
	(
	SELECT
		agg.*,
		dict.type AS type,
		dict.gmt_creator AS table2_gmt_creator 
	FROM
		monitor_region_agg agg
		LEFT JOIN monitor_dict dict ON agg.region_id = dict.region_id 
		AND agg.is_delete = '0' 
		AND dict.type IN ( 'attention', 'readStatus' ) 
		AND dict.gmt_creator = '32255' 
	WHERE
		1 = 1 
		AND agg.tenant_code = '00000007' 
		AND agg.business_code = '005' 
		AND agg.app_code = '005' 
	) region
	LEFT JOIN monitor_region_person_relation person ON region.region_id = person.region_id 
WHERE
	region.`is_delete` = '0' 
	AND person.`is_delete` = '0' 
	AND person.gather_flag = '0' 
	AND region.tenant_code = '00000007' 
	AND region.business_code = '005' 
	AND region.app_code = '005' 
	AND region.risk_date >= '2020-10-20' 
	AND region.risk_date <= '2021-01-20' 
GROUP BY
	region.region_id

结果：