无过滤条件的LEFT JOIN
SQL中最简单形式的LEFT JOIN,是直接根据关联字段,以左表为基准,对右表进行匹配。在SELECT语句中选取的字段,如果有右表的记录(一般都是需要右表的某些记录的),取出配对成功的右表记录中对应的这个字段的值;否则,直接置NULL。这本身就是LEFT JOIN的特点:保证左表记录完整,右表只是辅助匹配。
直接看例子,为了演示,准备了两张测试表test1,test2:
select * from test1
select * from test2
select t2.*,t1.market_place_id from test2 t2 left join test1 t1 on t2.parent_asin=t1.asin
对照上面例子解释一下这个结果:以左表test2为基准,用右表test1的asin字段和test2的parent_asin字段进行匹配,取出test2的全部数据和test1的market_place_id字段。对于test2中的第一条记录,因为右表中有两条记录符合的asin='parent1',只是market_place_id不同而已(分别为1、2)。于是这两条记录都会作为符合条件的记录加入结果集。这时,虽然是以左表为基准,但是这条记录却在结果集中产生了两条对应的记录。这点要稍加注意:以左表为基准并不意味着结果集的记录数量=左表的记录数量!
再回过头来看结果集的5条记录,由ID字段可以很好的区分出每条记录是由左表的哪条记录对应产生的。这里,最后两条记录可以很好的体现出LEFT JOIN的特点。
右表有滤条件的LEFT JOIN
这里,我们忽略左表有过滤条件场景的讨论,因为在LEFT JOIN中左表作为基准表,对他的过滤直接反应在SQL的WHERE字句中,效果上也相当于单表SELECT的WHERE字句过滤,缩小左表范围后,再和右表做JOIN,没什么悬念。
但是对于右表的过滤,通常有两种主要的方式:在ON字句中加入过滤条件或者在LEFT JOIN之后的WHERE字句中加入过滤条件。对于这两种方式的对比,下面主要针对逻辑语义和实现性能上加以对比。
- 过滤条件在ON字句中
select t2.*,t1.market_place_id from test2 t2 left join test1 t1 on t2.parent_asin=t1.asin and t1.market_place_id='2'上面这条SQL加上了对右表test1中market_place_id的过滤条件:只关心market_place_id为‘2’的右表记录。查询结果如下。
逻辑语义上,这个结果相当于右表test1首先进行了条件过滤,只剩下两条记录[(2,'parent1','2'),(3,'parent2','2')],然后左表test2和这个过滤之后的结果集进行无过滤条件的LEFT JOIN,于是得到了上图的结果。
性能上,来看一下这条语句的执行计划截图
可以看出,T1确实先以2为标准对market_place_id做了一次过滤,然后,在外层,再做原来的LEFT JOIN。由此可以证实上面逻辑语义结果的展示,同时也可以发现,就本例而言,如果能够在market_place_id上建立index,可以直接避免内层过滤对右表进行的全表扫描,从而提高整个SQL的执行效率。下图为在market_place_id上建立index之后,同样SQL语句的执行计划:
这里可以看出,原来的TABLE FULL SCAN 已经被换成了INDEX的RANGE SCAN,从而也直接导致了Oracle的优化器在最外层的Hash Join替换为了Nested Loops。(当然这个join的方式并不能说明什么问题,因为毕竟测试用的数据集太小,完全有可能在大数据集的真实情况下,优化器根据统计信息还是最终使用Hash Join算法)
- 过滤条件在WHERE子句中
相关推荐
本篇文章是对Oracle的left join中on和where的区别进行了详细的分析介绍,需要的朋友参考下
oracle中left join和right join的区别浅谈,需要的朋友可以参考一下
ORACLE的优化器共有3种 ... 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
left join 过滤条件写在on后面和写在where 后面的区别
oracle中sql语句(+)符号代表连接 (+)在=前边为右连接 (+)在=后边为左连接 SELECT a.*, b.* from a(+) = b就是一个右连接,等同于select a.*, b.* from a right join b SELECT a.*, b.* from a = b(+)就是一个左连接...
left join左外连接,不管左表是否在右表中有匹配行,都查询出来,右表所有列值为null right join 右外连接 inner join内连接,同join JOIN: 如果表中有至少一个匹配,则返回行 LEFT JOIN: 即使右表中没有匹配,也从...
oracle中左右连接left /right join 貌似不起作用,使用(+)代替。
彻底搞懂Oracle的左外连接和右外连接(以数据说话)
缺省情况下是inner join,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过图标让大家认识它们的区别。 现有两张表,Table A 是左边的表。Table B 是右边的表。其各有四条...
在left outer join左侧的表叫做左表,右侧的表叫做右表。(能够查询出关联数据和左表没有关联的数据,在左表的下方进行展示) 右外连接:使用right outer join,表示即使右表存在未关联数据,也被查询出来。在left ...
代码如下:select a.f_username from ( SELECT /*+parallel(gu,4)*/distinct gu.f_username FROM t_base_...playid=4 and gu.f_paymoney>=1500 ) A left join ( select from t_base_vip_customes and ((vu.f_passeddate
且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...
这是我个人通过长时间的实践与收集最后总结出来的基本关系表的连接操作实际应用,以及在某些情况下可以用普通的sql语句代替某些连接操作(即实现同样的功能)其中文件夹中已经包含了测试所需数据库,这是备份形式需...
oracle在Where子句中,可以对datetime、char、varchar字段类型的列用Like子句配合通配符选取那些“很像…”的数据记录,以下是可使用的通配符: % 零或者多个字符 _ 单一任何字符(下划线) \ 特殊字符 oracle...
ORACLE用户是学习ORACLE数据库中的基础知识,下面就介绍下类系统常用的默认ORACLE用户: 1. sys用户:超级用户,完全是个SYSDBA(管理数据库的人)。拥有dba,sysdba,sysoper等角色或权限。是oracle权限最高的用户,...
连接无非是这几个 --内连接和where相同 inner join ... left join --右向外连接, right join --完整外部连接, full join --交叉连接,也称笛卡儿积。 cross join .......
左连接 (left [outer] join) 左外连接就是将左表的所有数据分别于右表的每条数据进行连接组合,返回的结果除内连接的数据外,还有左表中不符合条件的数据,并在右表的相应列中填上null值。 SQL语句如下: select * ...
Oracle练习笔试大全 1、select ename, sal * 12 from emp; //计算年薪 2、select 2*3 from dual; //计算一个比较纯的数据用dual表 3、select sysdate from dual; //查看当前的系统时间 4、select ename, sal*12 ...