网站建设怎么弄,免费域名注册和免费建站,洞口网站建设,吉林网站建设哪家有目录
一、知识点
二、测试验证
三、引申 一、知识点 left join中关于where和on条件的知识点#xff1a;
多表left join 是会生成一张临时表。on后面#xff1a; 一般是对left join 的右表进行条件过滤#xff0c;会返回左表中的所有行#xff0c;而右表中没有匹配上的数…目录
一、知识点
二、测试验证
三、引申 一、知识点 left join中关于where和on条件的知识点
多表left join 是会生成一张临时表。on后面 一般是对left join 的右表进行条件过滤会返回左表中的所有行而右表中没有匹配上的数据直接补为null。on后面如果存在对左表的筛选条件无论条件真假仍返回左表中的所有行只是会影响右表的匹配值。即on中针对左表的限制条件只会影响右表的匹配内容并不影响返回的左表行数。where后面是对两表连接后的数据进行筛选一般是针对左表的筛选条件。 例如下面的sql关联表右表的筛选条件如果放置在on后面则下面sql的执行顺序是B先按照条件进行过滤再与A表关联
#主表A 关联表B 关联条件A.id B.id 筛选条件B.id 1
A left join B on A.id B.id and B.id 1;筛选条件如果放置在where后面则下面sql的执行顺序是A和B先关联基于生成的临时表再进行where条件过滤。
#主表A 关联表B 关联条件A.id B.id 过滤条件
A left join B on A.id B.id where A.id 1二、测试验证 创建t1 ,t2 两张表
create table if not exists test1( id int comment 用户id,name string comment 用户姓名);insert overwrite table test1
values (1,a1),(2,a2),(3,a3),(4,a4);create table if not exists test2( id int comment 用户id,province string comment 用户所在省份);insert overwrite table test1
values (1,hainan),(2,beijing),(5,sichuan),(6,chongqing);测试1返回左表所有行右表符合on条件可匹配上不满足条件的直接补nullselecttest1.id,test1.name,test2.id,test2.province
from test1
left join test2on test1.id test2.id; 测试2on后面增加对右表的限制条件结论左表记录全部返回右表筛选条件生效selecttest1.id,test1.name,test2.id,test2.province
from test1
left join test2on test1.id test2.id and test2.province beijing; 测试3where后面增加对右表的限制条件where test2.province beijing;结论where后面是对最后的临时表进行记录筛选行数可能会减少selecttest1.id,test1.name,test2.id,test2.province
from test1
left join test2on test1.id test2.id where test2.province beijing; 测试4on中增加对左表的限制条件不影响返回的行数只影响右表的匹配内容。selecttest1.id,test1.name,test2.id,test2.province
from test1
left join test2on test1.id test2.id and test1.name a1;
测试5where后面增加对左表的限制条件where test1.name a4 结论where条件是在最后临时表的基础上进行筛选返回满足条件的行selecttest1.id,test1.name,test2.id,test2.province
from test1
left join test2on test1.id test2.idwhere test1.name a4; 三、引申 由上述的where和on之间的区别引申出来的面试题 t1表和t2表的字段如图计算以下两个sql的值。
--1.输出结果
select t1.id,t2.id
from t1
left join t2 on t1.id t2.id and t2.id 2;--2.输出结果
select t1.id,t2.id
from t1
left join t2 on t1.id t2.id
where t2.id 2;解析
1on后面跟着的是对右表的限制条件 t2.id 2; 结论左表记录全部返回右表筛选条件生效 with t1 as (select 1 as id union allselect 2 as id union allselect 3 as id
),t2 as (select 1 as id union allselect 2 as id union allselect 2 as id)select t1.id,t2.id
from t1
left join t2 on t1.id t2.id and t2.id 2;输出结果为 2where后面跟着的是对右表的限制条件 where t2.id 2; 结论最后临时表的基础上进行筛选最终返回符合where过滤条件的行 ps: sql中比较的结果一般有true, false, null 而where条件只会过滤出true的结果。
with t1 as (select 1 as id union allselect 2 as id union allselect 3 as id
),t2 as (select 1 as id union allselect 2 as id union allselect 2 as id)select t1.id,t2.id
from t1
left join t2 on t1.id t2.id
where t2.id 2;
输出结果为 补充
select (12) -- FALSE
select (12) -- true
select (1null) -- null--所以sql比较的结果会有三种false, true, null
