SQL on and 和 on where 的区别

喜欢ヅ旅行 2023-08-17 15:31 142阅读 0赞

##  on and 和 on where 的 区别 ##

**在使用 left join 时, on and 和 on where 会有区别；  
1. on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录  
　　on 后面 and 都是对右表进行筛选**

**2.where是全部连接完后，对临时表进行筛选，筛选对左表和右表都有效**

在使用inner join时，on   and与on  where条件的区别：不管是对左表还是右表进行筛选，on  and和on  where都会对生成的临时表进行过滤。

1. 新建表 t1

![708681-20190730100217280-886048075.png][]

2. 新建表 t2

![708681-20190730100302702-400121432.png][]

3. 关联查询：

select * from t1 left join t2 on t1.user_id = t2.user_id

![708681-20190730100428221-1923893250.png][]

## on and  ##

a.) 对左表加and 条件

select * from t1 left join t2 on t1.user_id = t2.user_id and t1.deleted = 0

![708681-20190730100645608-263020629.png][]

b.) 对右表加and 条件

select * from t1 left join t2 on t1.user_id = t2.user_id and t2.deleted = 0

![708681-20190730100754752-686442167.png][]

##  on where ##

select * from t1 left join t2 on t1.user_id = t2.user_id where t1.deleted = 0

## ![708681-20190730101142981-172168493.png][] ##

select * from t1 left join t2 on t1.user_id = t2.user_id where t2.deleted = 0

![708681-20190730101313633-199900403.png][]

转载于:https://www.cnblogs.com/Uzai/p/11260361.html

[708681-20190730100217280-886048075.png]: /images/20230810/ba4704693bd64775be0ca2b1cdb99587.png
[708681-20190730100302702-400121432.png]: /images/20230810/e49828d9e4994681881f5a32d4389ad8.png
[708681-20190730100428221-1923893250.png]: /images/20230810/9c959c289462443f9459f96856604961.png
[708681-20190730100645608-263020629.png]: /images/20230810/87c28ad212d64aea9652f7f5d927b482.png
[708681-20190730100754752-686442167.png]: /images/20230810/c9ecfee2c86d4146af70c9ad0dd77d88.png
[708681-20190730101142981-172168493.png]: /images/20230810/1c3d2558f561415aa4179fc1613e6878.png
[708681-20190730101313633-199900403.png]: /images/20230810/8723fd4769b3408d8b613fe347d91395.png