LightDB 支持 (+) 操作符来指定外连接-CSDN博客

本文链接：https://blog.csdn.net/qq_17713935/article/details/130385380

LightDB 支持 (+) 操作符来指定外连接

LightDB-X 为了更好的兼容 oracle，从23.2开始支持oracle的 (+) 操作符来指定外连接方式。下面梳理下 (+) 的限制以及与oracle的异同。
LighDB-A 分支也支持此特性。

oracle (+) 简介

oracle (+) 操作符用于指定外连接，是 oracle 在 ANSI标准加入前就推出的，后面的版本支持 (+) 操作符只是为了向前兼容。oracle 不推荐使用（+）建议使用from 子句中的outer join，因为oracle (+) 具有一些限制。

用法

t1 right join t2 ,也即t2 left join t1.

explain select * from t1 a, t2 b where a.key1(+)=b.key1;
                          QUERY PLAN                             
--------------------------------------------------------------------
Merge Left Join  (cost=317.01..711.38 rows=25538 width=16)
Merge Cond: (b.key1 = a.key1)
->  Sort  (cost=158.51..164.16 rows=2260 width=8)
      Sort Key: b.key1
      ->  Seq Scan on t2 b  (cost=0.00..32.60 rows=2260 width=8)
->  Sort  (cost=158.51..164.16 rows=2260 width=8)
      Sort Key: a.key1
      ->  Seq Scan on t1 a  (cost=0.00..32.60 rows=2260 width=8)
(8 rows)

限制

只能用于select语句中的where clause中（可以是update中的select子查询）
精确匹配 (+)，3个字符，不能有空格。 — oracle 可以有空格

如果两个表间有多个关联条件，那么每个条件都要使用 (+)，这样才能把所有的关联条件作为join on 的关联条件。不然，没有使用 (+) 的条件会保留在where 中，然后会导致满足空值拒绝，最终被优化为 inner join(比如 left join 中的右表被where 条件约束为不能为null, 则等价与 inner join)。

explain select * from t1 a, t2 b where a.key1(+)=b.key1 and a.key2=b.key2;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Merge Join  (cost=317.01..352.19 rows=128 width=16)
   Merge Cond: ((a.key1 = b.key1) AND (a.key2 = b.key2))
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: a.key1, a.key2
         ->  Seq Scan on t1 a  (cost=0.00..32.60 rows=2260 width=8)
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: b.key1, b.key2
         ->  Seq Scan on t2 b  (cost=0.00..32.60 rows=2260 width=8)

(+) 操作符只能作用于字段，不能作用于表达式，但可以作用于表达式中的字段

select * from t1 a, t2 b where mod(a.key1,10)(+)=b.key1;
ERROR:  syntax error at or near "(+)"
LINE 1: select * from t1 a, t2 b where mod(a.key1,10)(+)=b.key1;
                                                     ^

explain select * from t1 a, t2 b where mod(a.key1(+),10)=b.key1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Merge Left Join  (cost=317.01..780.88 rows=25538 width=16)
   Merge Cond: (b.key1 = (mod(a.key1, 10)))
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: b.key1
         ->  Seq Scan on t2 b  (cost=0.00..32.60 rows=2260 width=8)
   ->  Sort  (cost=158.51..164.16 rows=2260 width=8)
         Sort Key: (mod(a.key1, 10))
         ->  Seq Scan on t1 a  (cost=0.00..32.60 rows=2260 width=8)
(8 rows)

使用了 (+) 操作符的约束不能与其他约束通过or 组合

select * from t1 a, t2 b where a.key1(+)=b.key1 or a.key2(+)=b.key2;
ERROR:  Operator "(+)" is not allowed used with "OR" together

不能将任何用 (+) 操作符标记的列与子查询进行比较

select * from t1 a, t2 b where a.key1(+)=(select key1 from t3);
ERROR:  Operator "(+)" can not be used in outer join with SubQuery.
LINE 1: select * from t1 a, t2 b where a.key1(+)=(select key1 from t...
                                                ^

不能彼此进行外关联，如下：

select * from t1 a, t2 b where a.key1(+)=b.key1 and b.key2(+)=a.key2;
ERROR:  Relation can't outer join with each other.

(+) 作用的字段所属的表需要在当前查询块，不能位于上层的查询块

select * from t1 a  where  exists (select * from t2 b where a.key1(+)=b.key1);
ERROR:  Operator "(+)" can't specify on "a" which cannot be referenced from this part of the query.
LINE 1: ...rom t1 a  where  exists (select * from t2 b where a.key1(+)=...
                                                             ^

(+) 操作符不能用于嵌套的and/or 表达式，示例如下：

select * from t1 a, t2 b where not (a.key1(+)= b.key1 and a.key2(+)=b.key2);
ERROR:  Operator "(+)" can not be used in nested and/or expression.
LINE 1: select * from t1 a, t2 b where not (a.key1(+)= b.key1 and a....
                                       ^
select * from t1 a, t2 b where not (a.key1(+)= b.key1 or a.key2(+)=b.key2);
ERROR:  Operator "(+)" can not be used in nested and/or expression.
LINE 1: select * from t1 a, t2 b where not (a.key1(+)= b.key1 or a.k...

oracle 不支持上述的 not (a.key1(+)= b.key1 and a.key2(+)=b.key2) ，但支持 not (a.key1(+)= b.key1 or a.key2(+)=b.key2)，这是因为not (a.key1(+)= b.key1 and a.key2(+)=b.key2) 实际就是上述的限制5。

lightdb 只要嵌套的表达式为and 或 or 表达式就不支持。

不能和 ansi join 一起用

select * from t1 a join t2 b on a.key1=b.key1, t3 c, t4 d where c.key1(+)=d.key1;
ERROR:  Operator "(+)" and Join in FromClause can't be used together

一个表不能同时right join 多个表 — oracle支持，oracle 会把被 join 的多个表转为 cross join

select * from t1 a, t2 b, t3 c where a.key1(+)=b.key1+c.key1;
ERROR:  "a" can't outer join with more than one relation
HINT:  "b", "c" are outer join with "a".

select * from t1 a, t2 b, t3 c where a.key1(+)=b.key1 and a.key1(+)=c.key1;
ERROR:  "a" can't outer join with more than one relation.

不支持指定full out join，如下

select * from t1 a, t2 b ,t3 c where a.key1(+) =b.key1(+);
ERROR:  Operator "(+)" can't be specified on more than one relation in one join condition
HINT:  "a", "b"...are specified Operator "(+)" in one condition.