转帖:http://hittyt.iteye.com/blog/1885141
无过滤条件的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'
逻辑语义上,这个结果相当于右表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子句中
- select t2.*,t1.market_place_id from test2 t2
- left join test1 t1
- on t2.parent_asin=t1.asin
- where t1.market_place_id='2'
相关推荐
本篇文章是对Oracle的left join中on和where的区别进行了详细的分析介绍,需要的朋友参考下
oracle中left join和right join的区别浅谈,需要的朋友可以参考一下
ORACLE的优化器共有3种 ... 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
left join 过滤条件写在on后面和写在where 后面的区别
oracle-join,java代码展示。
Oracle中hash join研究.pdf
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存储过程中使用临时表 会话级临时表 事务级临时表
[Oracle]在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引
ORACLE quota 表空间限制,主要内容为测试ORACEL数据库对于表空间quota的限制
oracle中左右连接left /right join 貌似不起作用,使用(+)代替。
彻底搞懂Oracle的左外连接和右外连接(以数据说话)
Oracle 临时表功能介绍: Oracle中的临时表是全局的,需要在数据库设计时创建完成,而不是程序使用时。每个登陆用户都使用这一个相同的临时表,但互相之间看不到彼此的数据,也就是说临时表是会话独立的。
oracle查询表碎片语句
oracle术语 oracle 词汇 中英文 對照表 oracle中英文词汇對照表 oracle中英文词汇對照表 oracle中英文词汇對照表
判断oracle数据库中是否存在新建表,如果存在则不创建表,不存在则执行建表语句;只需要传递建表语句就可以判断了