Logo
联邦查询

联邦查询 SQL 兼容性 #

在 SphereEx-DBPlusEngine SQL 解析引擎的基础上，联邦查询通过对 SQL 语法树的语义分析，并使用 RBO 和 CBO 优化生成最优执行计划，可以实现分布式场景下复杂 SQL 的支持。

目前联邦查询功能仅适配了 MySQL 数据库，其他数据库支持还不完善，需要进一步适配数据库方言函数和运算符，需要用户在使用过程中进行规避，下面展示了 SphereEx-DBPlusEngine 能够支持以及不支持的常用 SQL 列表，可供用户使用参考。

支持的常用 SQL 列表 #

MySQL #

  • 聚合查询：
SQL 示例说明
SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > ? ORDER BY max_price支持 SELECT GROUP BY HAVING 分组聚合查询及 MAX、MIN、SUM、AVG 和 COUNT 聚合函数
  • 集合查询（UNION | INTERSECT | EXCEPT）：
SQL 示例说明
SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION ALL SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id LIMIT 5, 5支持 UNION 查询
SELECT * FROM t_order INTERSECT ALL SELECT * FROM t_order ORDER BY order_id LIMIT 5, 5支持 INTERSECT 查询
SELECT * FROM t_order EXCEPT ALL SELECT * FROM t_order ORDER BY order_id支持 EXCEPT 查询
  • 关联查询（INNER JOIN、CROSS JOIN、STRAIGHT_JOIN、NATURAL JOIN、LEFT JOIN、RIGHT JOIN）：
SQL 示例说明
SELECT * FROM t_order o INNER JOIN t_user i ON o.user_id = i.user_id WHERE user_name LIKE ‘张%’支持 INNER JOIN 查询
SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 LIMIT 10, 10支持 CROSS JOIN 查询
SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id支持 NATURAL JOIN 查询
SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7支持 NATURAL LEFT JOIN 查询
SELECT * FROM t_product p LEFT JOIN t_product_detail d ON d.product_id = p.product_id WHERE p.category_id = ? ORDER BY p.product_id, 7支持 LEFT JOIN 查询
SELECT * FROM t_order o LEFT JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7支持 LEFT JOIN USING 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i ON o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN 查询
SELECT * FROM t_order o STRAIGHT_JOIN t_order_item i ON o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_id, 7支持 STRAIGHT_JOIN 查询
SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7支持 NATURAL RIGHT JOIN 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN USING 查询
  • 排序、分页语句：
SQL 示例说明
SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2支持排序、LIMIT 分页查询
  • 子查询语句（Projection、From 及 Where 中的子查询）：
SQL 示例说明
SELECT o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?支持投影列中的子查询
SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND t.order_id > ? ORDER BY item_id支持 From 中的子查询
SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的子查询
SELECT * FROM t_order o WHERE o.order_id > ALL (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的 ALL 子查询
SELECT * FROM t_order o WHERE o.order_id > ANY (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的 ANY 子查询
SELECT * FROM t_order o WHERE o.order_id <> SOME (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的 SOME 子查询
SELECT * FROM t_order o WHERE EXISTS (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的 EXISTS 子查询
SELECT * FROM t_order o WHERE NOT EXISTS (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的 NOT EXISTS 子查询

PostgreSQL #

  • 聚合查询：
SQL 示例说明
SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > ? ORDER BY max_price支持 SELECT GROUP BY HAVING 分组聚合查询及 MAX、MIN、SUM、AVG 和 COUNT 聚合函数
  • 集合查询（UNION | INTERSECT | EXCEPT）：
SQL 示例说明
SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id支持 UNION 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 INTERSECT 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 EXCEPT 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 UNION | INTERSECT | EXCEPT 混合查询
  • 关联查询（INNER JOIN、CROSS JOIN、NATURAL JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN）：
SQL 示例说明
SELECT * FROM t_order o INNER JOIN t_user i ON o.user_id = i.user_id WHERE user_name LIKE ‘张%’支持 INNER JOIN 查询
SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 FETCH NEXT 3 ROW ONLY支持 CROSS JOIN 查询
SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id支持 NATURAL JOIN 查询
SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7支持 NATURAL LEFT JOIN 查询
SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7支持 NATURAL RIGHT JOIN 查询
SELECT * FROM t_product p LEFT JOIN t_product_detail d ON d.product_id = p.product_id WHERE p.category_id = ? ORDER BY p.product_id, 7支持 LEFT JOIN 查询
SELECT * FROM t_order o LEFT JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7支持 LEFT JOIN USING 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i ON o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN USING 查询
SELECT * FROM t_order o FULL JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7支持 FULL JOIN 查询
  • 排序、分页语句：
SQL 示例说明
SELECT * FROM t_product p INNER JOIN t_product_detail d USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC FETCH NEXT 3 ROW ONLY支持排序、FETCH 分页查询
  • 子查询语句（Projection、From 及 Where 中的子查询）：
SQL 示例说明
SELECT o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?支持投影列中的子查询
SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND t.order_id > ? ORDER BY item_id支持 From 中的子查询
SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的子查询

openGauss #

  • 聚合查询：
SQL 示例说明
SELECT MAX(p.price) AS max_price, MIN(p.price) AS min_price, SUM(p.price) AS sum_price, AVG(p.price) AS avg_price, COUNT(1) AS count FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id INNER JOIN t_product p ON i.product_id = p.product_id GROUP BY o.order_id HAVING SUM(p.price) > ? ORDER BY max_price支持 SELECT GROUP BY HAVING 分组聚合查询及 MAX、MIN、SUM、AVG 和 COUNT 聚合函数
  • 集合查询（UNION | INTERSECT | EXCEPT | MINUS）：
SQL 示例说明
SELECT o.order_id, o.user_id FROM t_order o WHERE o.order_id > ? UNION SELECT i.order_id, i.user_id FROM t_order_item i WHERE i.order_id > ? ORDER BY order_id支持 UNION 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 INTERSECT 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 EXCEPT 查询
SELECT * FROM t_order MINUS SELECT * FROM t_order WHERE order_id = ?支持 MINUS 查询
SELECT * FROM t_order WHERE order_id = ? UNION SELECT * FROM t_order INTERSECT SELECT * FROM t_order WHERE order_id = ? EXCEPT SELECT * FROM t_order WHERE order_id = ? ORDER BY order_id支持 UNION | INTERSECT | EXCEPT | MINUS 混合查询
  • 关联查询（INNER JOIN、CROSS JOIN、NATURAL JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN）：
SQL 示例说明
SELECT * FROM t_order o INNER JOIN t_user i ON o.user_id = i.user_id WHERE user_name LIKE ‘张%’支持 INNER JOIN 查询
SELECT * FROM t_product p CROSS JOIN t_product_detail d WHERE p.product_id = ? ORDER BY d.product_id, 7 FETCH NEXT 3 ROW ONLY支持 CROSS JOIN 查询
SELECT * FROM t_order o NATURAL JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id支持 NATURAL JOIN 查询
SELECT * FROM t_order o NATURAL LEFT JOIN t_merchant m WHERE o.user_id = ? ORDER BY o.order_id, 7支持 NATURAL LEFT JOIN 查询
SELECT * FROM t_order o NATURAL RIGHT JOIN t_order_item i WHERE i.user_id = ? ORDER BY i.item_id, 7支持 NATURAL RIGHT JOIN 查询
SELECT * FROM t_product p LEFT JOIN t_product_detail d ON d.product_id = p.product_id WHERE p.category_id = ? ORDER BY p.product_id, 7支持 LEFT JOIN 查询
SELECT * FROM t_order o LEFT JOIN t_order_item i USING(order_id) WHERE o.user_id = ? ORDER BY o.order_id, 7支持 LEFT JOIN USING 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i ON o.order_id = i.order_id WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN 查询
SELECT * FROM t_order o RIGHT JOIN t_order_item i USING(order_id) WHERE i.user_id = ? ORDER BY i.item_id, 7支持 RIGHT JOIN USING 查询
SELECT * FROM t_order o FULL JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? OR i.user_id = ? ORDER BY o.order_id, 7支持 FULL JOIN 查询
  • 排序、分页语句：
SQL 示例说明
SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.user_id = ? ORDER BY o.order_id, 7 LIMIT 5, 2支持排序、LIMIT 分页查询
SELECT * FROM t_product p INNER JOIN t_product_detail d USING(product_id) WHERE p.product_id > ? ORDER BY p.product_id DESC FETCH NEXT 3 ROW ONLY支持排序、FETCH 分页查询
  • 子查询语句（Projection、From 及 Where 中的子查询）：
SQL 示例说明
SELECT o.order_id, o.user_id, o.status, o.merchant_id, (SELECT t.merchant_name FROM t_merchant t WHERE t.merchant_id = o.merchant_id) AS merchant_name FROM t_order o WHERE o.order_id = ?支持投影列中的子查询
SELECT * FROM (SELECT o.* FROM t_order o WHERE o.user_id IN (10, 11, 12)) AS t, t_order_item i WHERE t.order_id = i.order_id AND t.order_id > ? ORDER BY item_id支持 From 中的子查询
SELECT * FROM t_order o WHERE o.order_id IN (SELECT i.order_id FROM t_order_item i INNER JOIN t_product p ON i.product_id = p.product_id WHERE p.product_id = ?) ORDER BY order_id支持 Where 中的子查询

不支持的常用 SQL 列表 #

MySQL #

  • 视图查询：
SQL 示例说明
SELECT * FROM t_order_item_join_view暂不支持视图查询
  • CTE 查询：
SQL 示例说明
WITH test AS (SELECT * FROM t_order) SELECT * FROM test暂不支持 CTE 查询
  • SELECT … WITH ROLLUP:
SQL 示例说明
SELECT id, COUNT(*) FROM table GROUP BY id WITH ROLLUP暂不支持 SELECT … WITH ROLLUP 查询

PostgreSQL #

  • 视图查询：
SQL 示例说明
SELECT * FROM t_order_item_join_view暂不支持视图查询

openGauss #

  • 视图查询：
SQL 示例说明
SELECT * FROM t_order_item_join_view暂不支持视图查询