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

待我称王封你为后i 2023-01-09 01:23 122阅读 0赞

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

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

两条 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条件；  
![197ac8e2f0110574301c1023a0ef8293.png][]  
2、再对中间表过滤where条件：tab2.name=AAA'tab1.id tab1.size tab2.size tab2.name  
![535ef24d90e4902e8e9d10ffd4cbbee6.png][]

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

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

示例二：

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

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pZQzg4ODg4_size_16_color_FFFFFF_t_70][]

二、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

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pZQzg4ODg4_size_16_color_FFFFFF_t_70 1][]

其实以上结果的关键原因

left join，right join，full join 的特殊性，不管on上的条件是否为真都会返回1eft或right表中的记录，ful1则具有left和right的特性的并集。

而inner join 没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

on为了反映外连接中一方的全连接，而where没有这个功能，内连接配对是可以的。

结论  
**1.where 是在两个表join完成后，再附上where条件**

**2.而 and 则是在表连接前过滤A表或B表里面哪些记录符合连接条件，同时会兼顾是left join还是right join。**

**即假如是左连接的话，如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NULL）。on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。**

**3.而inner join 没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。**

**4.建议尽量用where来过滤条件。**

[62b8baf4a194659500a1c1c33ccb4ce4.png]: /images/20221119/0ea0c8fee2b44eb5983bd6b6a7e952b6.png
[197ac8e2f0110574301c1023a0ef8293.png]: /images/20221119/43f65db363bc44909e9e961157e7e928.png
[535ef24d90e4902e8e9d10ffd4cbbee6.png]: /images/20221119/921b6febbecd4bdfa0c00751a0b5ba91.png
[b983ad2d61dc16be3601fbb4b94d7ca4.png]: /images/20221119/3aff5b5cab004df0b9b10d929b440236.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pZQzg4ODg4_size_16_color_FFFFFF_t_70]: /images/20221119/9b8946f6b896497b93a2888df694385e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pZQzg4ODg4_size_16_color_FFFFFF_t_70 1]: /images/20221119/6999893d50a6449fa16232aabaca15e9.png