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