达梦-左右连接

左右连接

左外连接：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。

标准写法：

a left join b on a.order_id = b.order_id

兼容oracle写法：

a.order_id = b.order_id(+)

右外连接：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。

标准写法：

a right join b on a.order_id = b.order_id

兼容oracle写法：

1. order_id(+) = b.order_id

数据准备

drop table t1; drop table t2; create table t1 (c1 int,c2 varchar2(10)); create table t2 (c1 varchar2(10),c2 int); insert into t1 values(1,'A'); insert into t1 values(2,'B'); insert into t1 values(3,'C'); insert into t2 values('A',10); insert into t2 values('B',20); insert into t2 values('D',30); commit;

例1：

select * from t1 left join t2 on t1.c2=t2.c1 and t2.c2=20;   --可以看到走的是哈希右外链接。

从这个查询SQL来看，因为是左连接，所以t1表的所有数据肯定都会返回（无论on的条件真假）。jion的条件有两个，一个是t1.c2=t2.c1,一个是t2.c2=20。

但是从执行计划来看，先执行t2将t2.c2=20的行筛选出来，然后将t2作为左表，与t1表做hash right join。步骤如下：

第一步

select * from t2 where t2.c2=20;

c1	c2
B	20

第二步

select * from t1;

c1	c2
1	A
2	B
3	C

第三步

select t1.c1,t1.c2,temp.c1,temp.c2 from

（select * from t2 where t2.c2=20）temp

right join t1

on temp.c1=t1.c2;

 

从第一步可以看出来，t2.c2=20这个条件，并不会影响t1表的返回值。

如果我们将t2表数据做的很大

truncate table t2; begin for i in 0..100000 loop insert into T2 values('A'||i,i+10); end loop; end; --设置一条符合t1.c2=t2.c1 and t2.c2=20的数据 update t2 set c1='A' where c2=20; commit;

再次查看执行计划

select  * from t1 left join t2 on t1.c2=t2.c1 and t2.c2=20;

可以看到并没有做左右连接的转换。其实很容易想到优化器会选择较小的表作为左表。

我们再来造一个t1表很大的表来看下效果

drop table t1; drop table t2; create table t1 (c1 int,c2 varchar2(10)); create table t2 (c1 varchar2(10),c2 int); insert into t1 values(1,'A'); begin for i in 1..100000 loop insert into T1 values(i+1,'A'||i); end loop; end; insert into t2 values('A',10); insert into t2 values('B',20); insert into t2 values('D',30); commit;

t1表有100001条数据，t2表有3条数据。

select  * from t1 left join t2 on t1.c2=t2.c1 and t2.c2=20;   --强制走t1，t2表连接顺序 select  /*+ USE_HASH(T1, T2) */* from t1 left join t2 on t1.c2=t2.c1 and t2.c2=20;

可以看到（t1、t2）表做hash代价要高于（t2、t1）表连接。

例2：

select * from t1 left join t2 on t1.c2=t2.c1 where t2.c2=20;

从执行计划可以看出来，这里走的是hash inner连接。其实很容易想到

这个sql等价于

select * from t1 join t2 on t1.c2=t2.c1 and t2.c2=20;

关于on与where总结：

对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面。

对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。