当前位置： 移动技术网 > IT编程>数据库>MSSQL > sql连接查询中on筛选与where筛选的区别

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

2020年07月14日  | 移动技术网IT编程  | 我要评论

– 建表：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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网