| 含(+)操作符表达式 | 部分支持,有差异 | - 对于使用OR操作符连接的外连接表达式,GaussDB仅支持OR操作符左右两侧均为同一单表的外连接表达式。
在(+)和OR操作符混用的场景下,GaussDB与Oracle的行为存在差异,以下是与Oracle 19c的对比说明: 公共语句 -- 执行以下建表语句并插入数据
CREATE TABLE a(id int, b int);
CREATE TABLE a2(id int, c int);
INSERT INTO a VALUES(1,1);
INSERT INTO a VALUES (null, 11);
INSERT INTO a VALUES (2, null);
INSERT INTO a VALUES (123,123);
INSERT INTO a VALUES (5, 500);
INSERT INTO a VALUES (null, 600);
INSERT INTO a2 VALUES(1,2);
INSERT INTO a2 VALUES (3,null);
INSERT INTO a2 VALUES (null, 22);
INSERT INTO a2 VALUES (234,234);
INSERT INTO a2 VALUES (5, 700);
INSERT INTO a2 VALUES (null, 800); 示例一 SELECT * FROM a,a2 WHERE (a.id(+) = 1 AND a.id(+) <= 2) OR a2.id(+) = 1 ORDER BY 1,2,3,4; GaussDB: -- 执行公共语句后再执行查询语句
gaussdb=# SELECT * FROM a,a2 WHERE (a.id(+) = 1 AND a.id(+) <= 2) OR a2.id(+) = 1 ORDER BY 1,2,3,4;
ERROR: Operator "(+)" is not allowed used with "OR" together
-- 清理本次示例新建的数据表(需按照实际情况修改表名)
gaussdb=# DROP TABLE a;
gaussdb=# DROP TABLE a2; Oracle: -- 执行公共语句后再执行查询语句
SQL> SELECT * FROM a,a2 WHERE (a.id(+) = 1 AND a.id(+) <= 2) OR a2.id(+) = 1 ORDER BY 1,2,3,4;
ID| B| ID| C
----------|----------|----------|----------
1| 1| 1| 2
1| 1| 3|
1| 1| 5| 700
1| 1| 234| 234
1| 1| | 22
1| 1| | 800
2| | 1| 2
5| 500| 1| 2
123| 123| 1| 2
| 11| 1| 2
| 600| 1| 2
11 rows selected.
-- 清理本次示例新建的数据表(需按照实际情况修改表名)
SQL> DROP TABLE a;
SQL> DROP TABLE a2; GaussDB因不满足OR操作符左右两侧为同一单表的外连接表达式的限制,报错;Oracle不报错。 示例二 SELECT * FROM a,a2 WHERE a.id(+) = a2.id AND a.id(+) != 2 OR a.id(+) IS NULL AND a.id(+) = a2.id ORDER BY 1,2,3,4; GaussDB: -- 执行公共语句后再执行查询语句
gaussdb=# SELECT * FROM a,a2 WHERE a.id(+) = a2.id OR a.id IS NULL AND a.id(+) = a2.id ORDER BY 1,2,3,4;
id | b | id | c
----+-----+-----+-----
1 | 1 | 1 | 2
5 | 500 | 5 | 700
| | 3 |
| | 234 | 234
| | | 22
| | | 800
(6 rows)
-- 提取公共表达式后,执行转化后的SQL语句
gaussdb=# SELECT * FROM a,a2 WHERE a.id(+) = a2.id AND (a.id(+) != 2 OR a.id(+) IS NULL) ORDER BY 1,2,3,4;
id | b | id | c
----+-----+-----+-----
1 | 1 | 1 | 2
5 | 500 | 5 | 700
| | 3 |
| | 234 | 234
| | | 22
| | | 800
(6 rows)
-- 清理本次示例新建的数据表(需按照实际情况修改表名)
gaussdb=# DROP TABLE a;
gaussdb=# DROP TABLE a2; Oracle: -- 执行公共语句后再执行查询语句
SQL> SELECT * FROM a,a2 WHERE a.id(+) = a2.id OR a.id IS NULL AND a.id(+) = a2.id ORDER BY 1,2,3,4;
ERROR at line 1:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
-- 提取公共表达式后,执行转化后的SQL语句
SQL> SELECT * FROM a,a2 WHERE a.id(+) = a2.id AND (a.id(+) != 2 OR a.id(+) IS NULL) ORDER BY 1,2,3,4;
ID| B| ID| C
----------|----------|----------|----------
1| 1| 1| 2
5| 500| 5| 700
| | 3|
| | 234| 234
| | | 22
| | | 800
6 rows selected.
-- 清理本次示例新建的数据表(需按照实际情况修改表名)
SQL> DROP TABLE a;
SQL> DROP TABLE a2; GaussDB因满足OR操作符左右两侧均为同一单表的外连接表达式,因此不报错;Oracle则直接报错(ORA-01719)。 通过提取公共表达式a.id(+) = a2.id,转化后的SQL语句为 SELECT * FROM a,a2 WHERE a.id(+) = a2.id AND (a.id(+) != 2 OR a.id(+) IS NULL) ORDER BY 1,2,3,4; 在两种数据库中执行结果一致。 - GaussDB不支持在MERGE INTO语句的WHERE子句中使用带(+)的外连接表达式,而Oracle支持。
以下是与Oracle 19c的对比用例: GaussDB: -- 执行以下建表语句并插入数据
gaussdb=# DROP TABLE IF EXISTS test_tb1;
gaussdb=# DROP TABLE IF EXISTS test_tb2;
gaussdb=# CREATE TABLE test_tb1 (c1 INT, c2 INT, c3 INT);
gaussdb=# CREATE TABLE test_tb2 (c1 INT, c2 INT, c3 INT);
gaussdb=# INSERT INTO test_tb1 VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300);
gaussdb=# INSERT INTO test_tb2 VALUES (1, 10, 100), (2, 20, 200), (4, 40, 400);
-- 执行MERGE INTO,其中WHEN MATCHED THEN后的SQL子句中WHERE含(+)操作符表达式
gaussdb=# MERGE INTO test_tb2
gaussdb-# USING test_tb1
gaussdb-# ON (test_tb1.c1 = test_tb2.c2)
gaussdb-# WHEN MATCHED THEN
gaussdb-# UPDATE SET test_tb2.c1 = test_tb1.c2 WHERE test_tb1.c3 = test_tb2.c3(+);
ERROR: Operator "(+)" can only be used in WhereClause of Select-Statement or Subquery.
LINE 5: ...set test_tb2.c1 = test_tb1.c2 where test_tb1.c3 = test_tb2.c...
^ Oracle: -- 执行MERGE INTO,其中WHEN MATCHED THEN后的SQL子句中WHERE含(+)操作符表达式
SQL> MERGE INTO test_tb2
USING test_tb1
ON (test_tb1.c1 = test_tb2.c2)
WHEN MATCHED THEN
UPDATE SET test_tb2.c1 = test_tb1.c2 WHERE test_tb1.c3 = test_tb2.c3(+);
Affected rows:10 |