Oracle兼容-语法-FULL JOIN

1. 语法

在GreatSQL中支持 全连接(FULL JOIN)。目前除了暂不支持 USING() 语法外，FULL JOIN 与既有的 LEFT/RIGHT JOIN 的使用方式完全一致。

FULL OUTER JOIN 与 FULL JOIN 等价。

参考语法如下：

  1. table_references:
  2.     escaped_table_reference [, escaped_table_reference] ...
  4. escaped_table_reference: {
  5.     table_reference
  6.   | { OJ table_reference }
  7. }
  9. table_reference: {
  10.     table_factor
  11.   | joined_table
  12. }
  14. ...
  16. joined_table: {
  17.   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  18.   | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
  19.   | table_reference FULL [OUTER] JOIN table_reference ON search_condition
  20. }
  22. join_specification: {
  23.     ON search_condition
  24.   | USING (join_column_list)
  25. }

2. Oracle兼容说明

    1. 不支持 USING() 语法
  1. greatsql> SELECT * FROM t1 FULL JOIN t2 USING(c1);
  2. ERROR 1235 (42000): This version of MySQL doesn't yet support 'FULL JOIN with USING clause'
    1. 不支持与外连接符号 (+) 同时使用
  1. greatsql> SELECT * FROM t1 FULL JOIN t2 ON t1.c1=t2.c1(+);
  2. ERROR 1235 (42000): This version of MySQL doesn't yet support 'Full join used together with outer join sign '(+)''

3. 示例

  1. -- 创建测试表并初始化数据
  2. greatsql> CREATE TABLE t1(c1 INT, c2 VARCHAR(10));
  3. greatsql> CREATE TABLE t2(c1 INT, c2 VARCHAR(10));
  4. greatsql> CREATE TABLE t3(c1 INT, c2 VARCHAR(10));
  6. greatsql> INSERT INTO t1 VALUES(1, 't1_row1'), (2, 't1_row2');
  7. greatsql> INSERT INTO t2 VALUES(1, 't2_row1'), (3, 't2_row3'), (4, 't2_row4');
  8. greatsql> INSERT INTO t3 VALUES(3, 't3_row3');
  10. greatsql> SELECT * FROM t1 FULL JOIN t2 ON t1.c1=t2.c1;
  11. +------+---------+------+---------+
  12. | c1   | c2      | c1   | c2      |
  13. +------+---------+------+---------+
  14. |    1 | t1_row1 |    1 | t2_row1 |
  15. |    2 | t1_row2 | NULL | NULL    |
  16. | NULL | NULL    |    3 | t2_row3 |
  17. | NULL | NULL    |    4 | t2_row4 |
  18. +------+---------+------+---------+
  20. greatsql> SELECT * FROM t1 FULL JOIN (t2 FULL JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t2.c1;
  21. +------+---------+------+---------+------+---------+
  22. | c1   | c2      | c1   | c2      | c1   | c2      |
  23. +------+---------+------+---------+------+---------+
  24. |    1 | t1_row1 |    1 | t2_row1 | NULL | NULL    |
  25. |    2 | t1_row2 | NULL | NULL    | NULL | NULL    |
  26. | NULL | NULL    |    3 | t2_row3 |    3 | t3_row3 |
  27. | NULL | NULL    |    4 | t2_row4 | NULL | NULL    |
  28. +------+---------+------+---------+------+---------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx