sql连接查询中on筛选与where筛选的区别

– 建表：main

create table main
as SELECT *
from (
SELECT 1 as id,"张三" as name,'男' as gender,20 as age
union SELECT 2 as id,"李四" as name,'男' as gender,21 as age
union SELECT 3 as id,"王五" as name,'男' as gender,22 as age
union SELECT 4 as id,"翠花" as name,'女' as gender,20 as age
union SELECT 5 as id,"如花" as name,'女' as gender,20 as age
union SELECT 6 as id,"春花" as name,'女' as gender,20 as age
) t

– 建表：ext

create table ext
as SELECT *
from (
SELECT 1 as id,"北京" as address
union SELECT 2 as id,"杭州" as address
union SELECT 3 as id,"天津" as address
union SELECT 4 as id,"南昌" as address
union SELECT 5 as id,"合肥" as address
union SELECT 6 as id,"上海" as address
) t

– 测试代码：

SELECT *
from main ,ext

test1：

select * from main 
left JOIN ext on main.id = ext.id and address <>'北京';

test2：

select * from main 
left JOIN ext on main.id = ext.id and name <>'王五';

test3：

select * from main 
left JOIN ext on main.id = ext.id  
where address <> '北京';

参考文献：https://mp.weixin.qq.com/s/r-yRD8OhmJ2T1JsDbQ0_hg

本文地址：https://blog.csdn.net/weixin_44976611/article/details/107299353