§ Oracle兼容-语法-FULL JOIN
§ 1. 语法
在GreatSQL中支持 全连接(FULL JOIN)。目前除了暂不支持 USING() 语法外，FULL JOIN 与既有的 LEFT/RIGHT JOIN 的使用方式完全一致。
FULL OUTER JOIN 与 FULL JOIN 等价。
参考语法如下：
table_references:
escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference: {
table_reference
| { OJ table_reference }
}
table_reference: {
table_factor
| joined_table
}
...
joined_table: {
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
| table_reference FULL [OUTER] JOIN table_reference ON search_condition
}
join_specification: {
ON search_condition
| USING (join_column_list)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
§ 2. Oracle兼容说明
- 不支持
USING()语法
- 不支持
greatsql> SELECT * FROM t1 FULL JOIN t2 USING(c1);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'FULL JOIN with USING clause'
1
2
2
- 不支持与外连接符号
(+)同时使用
- 不支持与外连接符号
greatsql> SELECT * FROM t1 FULL JOIN t2 ON t1.c1=t2.c1(+);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'Full join used together with outer join sign '(+)''
1
2
2
§ 3. 示例
-- 创建测试表并初始化数据
greatsql> CREATE TABLE t1(c1 INT, c2 VARCHAR(10));
greatsql> CREATE TABLE t2(c1 INT, c2 VARCHAR(10));
greatsql> CREATE TABLE t3(c1 INT, c2 VARCHAR(10));
greatsql> INSERT INTO t1 VALUES(1, 't1_row1'), (2, 't1_row2');
greatsql> INSERT INTO t2 VALUES(1, 't2_row1'), (3, 't2_row3'), (4, 't2_row4');
greatsql> INSERT INTO t3 VALUES(3, 't3_row3');
greatsql> SELECT * FROM t1 FULL JOIN t2 ON t1.c1=t2.c1;
+------+---------+------+---------+
| c1 | c2 | c1 | c2 |
+------+---------+------+---------+
| 1 | t1_row1 | 1 | t2_row1 |
| 2 | t1_row2 | NULL | NULL |
| NULL | NULL | 3 | t2_row3 |
| NULL | NULL | 4 | t2_row4 |
+------+---------+------+---------+
greatsql> SELECT * FROM t1 FULL JOIN (t2 FULL JOIN t3 ON t2.c1=t3.c1) ON t1.c1=t2.c1;
+------+---------+------+---------+------+---------+
| c1 | c2 | c1 | c2 | c1 | c2 |
+------+---------+------+---------+------+---------+
| 1 | t1_row1 | 1 | t2_row1 | NULL | NULL |
| 2 | t1_row2 | NULL | NULL | NULL | NULL |
| NULL | NULL | 3 | t2_row3 | 3 | t3_row3 |
| NULL | NULL | 4 | t2_row4 | NULL | NULL |
+------+---------+------+---------+------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
扫码关注微信公众号
