# SQL查询和子查询 GaussDB数据库兼容除分层查询以外的SQL查询和子查询。 表1SQL查询和子查询 | Oracle数据库 | GaussDB数据库 | 差异 | |:---|:---|:---| | 创建简单查询 | 支持 | - | | 分层查询 | 不支持 | - | | UNION \[ALL\]，INTERSECT，减运算符 | 支持 | - | | 查询结果排序 | 支持，有差异 | GaussDB查询不包含分组，且目标列同时包含聚集函数和集合返回函数时，不忽略对集合返回函数列的排序。 当查询列涉及类型转换且排序键为原始列名时，GaussDB因转换结果默认别名为原始列名，会按转换后的类型排序；Oracle因转换结果默认别名为转换表达式，仍按原始列的类型排序。两者排序语义不同，可能导致结果集顺序不一致。 | | Joins | 支持，有差异 | GaussDB只支持和Oracle相同的Join Types，如left、right、self 、natural、full outer join等。不支持In-Memory Join Groups等Join Optimizations方法。 | | 使用子查询 | 支持 | - | | 嵌套子查询的解嵌套 | 支持，有差异 | GaussDB不支持显式指定HASH_AJ或MERGE_AJ。 | | 分布式查询 | 支持，有差异 | GaussDB需要显式DBLINK查询。 | | 聚集函数嵌套 | 支持 | - | | 层次查询 | 支持，有差异 | - GaussDB在非CONNECT BY子句中使用PRIOR关键字修饰列时，列回显名称与Oracle有差异。例如对于列a，GaussDB回显为__prior_a__，Oracle回显为PRIORA。 - 在SELECT的GROUP BY子句中使用带有PRIOR列的表达式，GaussDB表现与Oracle有差异。GaussDB支持在GROUP BY子句中使用带有PRIOR列的表达式，Oracle会报错。 ``` -- GaussDB: gaussdb=# CREATE TABLE test (c1 INT, c2 INT, c3 INT); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE gaussdb=# INSERT INTO test VALUES(1,2,3); INSERT 0 1 gaussdb=# INSERT INTO test VALUES(2,3,4); INSERT 0 1 gaussdb=# SELECT PRIOR c1 + 1 AS result FROM test CONNECT BY PRIOR c2 = c1 GROUP BY PRIOR c1 + 1; result -------- 2 (2 rows) gaussdb=# DROP TABLE test; DROP TABLE -- Oracle: SQL> CREATE TABLE test (c1 INT, c2 INT, c3 INT); SQL> INSERT INTO test VALUES(1,2,3); SQL> INSERT INTO test VALUES(2,3,4); -- 该操作会报错not a GROUP BY expression SQL> SELECT PRIOR c1 + 1 AS result FROM test CONNECT BY PRIOR c2 = c1 GROUP BY PRIOR c1 + 1; SQL> DROP TABLE test; ``` - GaussDB语法上允许ORDER SIBLINGS BY和ORDER BY同时使用，此时最终输出的排序结果以外层的ORDER BY为准，Oracle不支持该用法。 | 目前xmltable_clause实现的功能与Oracle数据库和PG数据库还存在如下差异，如[表2]所示。 表2GaussDB与ORA数据库和PG数据库对比 | GaussDB | PG数据库 | Oracle数据库 | |:---|:---|:---| | 在row_expression处及columns_clause中的PATH处使用XPath 1.0表达式。 | 与GaussDB一致。 | 在与row_expression和columns_clause中的PATH对应处使用XQuery 1.0表达式。 | | 不支持默认命名空间功能。 | 与GaussDB一致。 | 支持默认命名空间功能。 | | 支持在passing_clause子句中传入单个数据但不可取别名。 | 与GaussDB一致（注意传入数据为XML类型）。 | 支持在对应子句部分传入多个数据且可取别名。 | | 支持省略passing_clause子句。 | 不支持省略passing_clause子句。 | 支持省略对应子句。 | | 不支持在passing_clause子句后使用RETURNING SEQUENCE BY REF子句。 | 与GaussDB一致。 | 支持在对应子句后使用RETURNING SEQUENCE BY REF子句。 | | 不支持在columns_clause子句中使用( SEQUENCE ) BY REF修饰XMLTYPE类型的返回数据。 | 与GaussDB一致。 | 支持在对应子句中使用( SEQUENCE ) BY REF修饰XMLTYPE类型的返回数据。 | | 在省略columns_clause子句中的PATH部分时，若不使用""将列名包起，在传入XMLTABLE的数据中对应节点的节点名为小写的情况下能正确找到该节点进行后续操作。 | 与GaussDB一致。 | 在省略对应部分时，若不使用""将列名包起，在传入XMLTABLE的数据中对应节点的节点名为大写的情况下能正确找到该节点进行后续操作。 | | 不支持省略columns_clause子句中列类型的声明。 | 与GaussDB一致。 | 支持省略对应子句中列类型的声明。 | | 在ORA兼容模式下支持省略columns_clause子句，在PG兼容模式下不支持省略columns_clause子句。 | 不支持省略对应子句。 | 支持省略对应子句。 | | 在ORA兼容模式下返回数据长度超过类型的typmod时会截断处理，在PG兼容模式下返回数据长度超过类型的typmod时会报错。 | 返回数据长度超过类型的typmod时会报错。 | 返回数据长度超过类型的typmod时会截断处理。 | 示例： ``` gaussdb=# CREATE DATABASE test DBCOMPATIBILITY = 'ORA'; gaussdb=# \c test test=# SELECT * FROM XMLTABLE( test, -- 声明两个XML的命名空间'nspace1'和'nspace2'及对应的别名"ns1"和"ns2" test(# '/ns1:root/*:child' -- 经row_expression从传入的数据中选取命名空间为'nspace1'的root节点，在选取其下面的所有child节点，忽略child的命名空间；其中ns1为'nspace1'的别名 test(# PASSING xmltype( test(# ' test'# test'# peter test'# 11 test'# test'# test'# qiqi test'# 12 test'# test'# test'# hacker test'# 15 test'# test'# ') test(# COLUMNS test(# columns FOR ORDINALITY, -- 该列为行号列 test path 'ns1:name', -- 从row_expression获取的每个child节点中选取命名空间为'nspace1'的name节点，并将节点中的值转换为varchar(10)返回；其中ns1为'nspace1'的别名 test; -- 从row_expression获取的每个child节点中选取age节点，并将节点中的值转换为int返回；由于第一个child节点没有显式指明命名空间，故跟随父节点root的命名空间，故该列一个值都没有返回 column | name | age --------+-------+----- 1 | peter | 2 | qiqi | 3 | | (3 rows) --切换至默认数据库并删除test数据库（需按照实际情况修改数据库名）。 gaussdb=# \c postgres gaussdb=# DROP DATABASE test; ```