§ Oracle兼容-语法-Oracle(+)语法

§ 1. 语法

-- LEFT JOIN
SELECT * FROM join_table1, join_table2 WHERE join_table1.col1 join_specification join_table2.col2(+);

-- RIGHT JOIN
SELECT * FROM join_table1, join_table2 WHERE join_table1.col1(+) join_specification join_table2.col2;

1
2
3
4
5

§ 2. `(+)`语法使用示例及说明

创建测试表并填充测试数据

greatsql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, c1 INT);
greatsql> CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, c1 INT);
greatsql> INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
greatsql> INSERT INTO t2 VALUES (4, 4), (3, 3), (2,2), (1,1);

greatsql> SELECT * FROM t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

greatsql> SELECT * FROM t2;
+----+------+
| id | c1   |
+----+------+
|  4 |    4 |
|  3 |    3 |
|  2 |    2 |
|  1 |    1 |
+----+------+
4 rows in set (0.00 sec)

greatsql> SELECT * FROM t1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

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
29
30
31
32
33
34
35
36
37

1. 支持多种用法。


-- `(+)` 写在被连接表一侧，表示 t1 LEFT JOIN t2
greatsql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1(+);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

-- 可以看到是t1 left join t2
greatsql> SHOW WARNINGS;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `greatsql`.`t1`.`id` AS `id`,`greatsql`.`t1`.`c1` AS `c1`,`greatsql`.`t2`.`id` AS `id`,`greatsql`.`t2`.`c1` AS `c1` from `greatsql`.`t1` left join `greatsql`.`t2` on((`greatsql`.`t2`.`c1` = `greatsql`.`t1`.`c1`)) where true

-- `(+)` 写在主动连接表一侧，表示 t1 RIGHT JOIN t2
greatsql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 (+)= t2.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

-- 可以看到是t2 left join t1
greatsql> SHOW WARNINGS;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `greatsql`.`t1`.`id` AS `id`,`greatsql`.`t1`.`c1` AS `c1`,`greatsql`.`t2`.`id` AS `id`,`greatsql`.`t2`.`c1` AS `c1` from `greatsql`.`t2` left join `greatsql`.`t1` on((`greatsql`.`t1`.`c1` = `greatsql`.`t2`.`c1`)) where true

-- 支持多表JOIN
greatsql> CREATE TABLE t3 LIKE t1;
greatsql> INSERT INTO t3 SELECT * FROM t1;

-- 等同于 t3 left join t2 left join t1
greatsql> EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.c1(+) = t2.c1 AND t2.c1(+) = t3.c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

greatsql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `greatsql`.`t1`.`id` AS `id`,`greatsql`.`t1`.`c1` AS `c1`,`greatsql`.`t2`.`id` AS `id`,`greatsql`.`t2`.`c1` AS `c1`,`greatsql`.`t3`.`id` AS `id`,`greatsql`.`t3`.`c1` AS `c1` from `greatsql`.`t3` left join (`greatsql`.`t2` left join `greatsql`.`t1` on((`greatsql`.`t1`.`c1` = `greatsql`.`t2`.`c1`))) on((`greatsql`.`t2`.`c1` = `greatsql`.`t3`.`c1`)) where true

-- 支持多表部分JOIN
greatsql> CREATE TABLE t4 LIKE t1;
greatsql> INSERT INTO t4 SELECT * FROM t1;

-- 等同于 (t2 left join t1) join (t3 left join t4)
greatsql> EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.c1(+) = t2.c1 AND t3.c1 = t4.c1(+);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using join buffer (hash join)              |
|  1 | SIMPLE      | t4    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

greatsql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `greatsql`.`t1`.`id` AS `id`,`greatsql`.`t1`.`c1` AS `c1`,`greatsql`.`t2`.`id` AS `id`,`greatsql`.`t2`.`c1` AS `c1`,`greatsql`.`t3`.`id` AS `id`,`greatsql`.`t3`.`c1` AS `c1`,`greatsql`.`t4`.`id` AS `id`,`greatsql`.`t4`.`c1` AS `c1` from `greatsql`.`t3` left join `greatsql`.`t4` on((`greatsql`.`t4`.`c1` = `greatsql`.`t3`.`c1`)) join `greatsql`.`t2` left join `greatsql`.`t1` on((`greatsql`.`t1`.`c1` = `greatsql`.`t2`.`c1`)) where true

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

1. (+) 只能用在单列上，不能用于表达式或者常量。

-- 错误示例，报告语法错误
greatsql> SELECT * FROM t1, t2 WHERE t1.c1 = 0(+);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(+)' at line 1

greatsql> SELECT * FROM t1, t2 WHERE (t1.c1 + t2.c1)(+) = t2.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(+) = t2.id' at line 1

1
2
3
4
5
6

1. (+) 只能用于 WHERE 表达式中的列，用在其它位置的列会忽略其作用。

-- 下面的示例不会按照t2.c1列排序或分组
greatsql> SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 ORDER BY t2.c1(+);
+----+------+----+------+
| id | c1   | id | c1   |
+----+------+----+------+
|  1 |    1 |  1 |    1 |
|  2 |    2 |  2 |    2 |
|  3 |    3 |  3 |    3 |
|  4 |    4 |  4 |    4 |
+----+------+----+------+
4 rows in set (0.00 sec)

greatsql> SELECT t2.c1 FROM t1, t2 WHERE t1.c1 = t2.c1 GROUP BY t2.c1(+);
+------+
| c1   |
+------+
|    4 |
|    3 |
|    2 |
|    1 |
+------+
4 rows in set (0.00 sec)

greatsql> SELECT t1.c1, t2.c1 FROM t1, t2 GROUP BY t2.c1, t1.c1 HAVING t2.c1 = t1.c1(+);
+------+------+
| c1   | c1   |
+------+------+
|    4 |    4 |
|    3 |    3 |
|    2 |    2 |
|    1 |    1 |
+------+------+
4 rows in set (0.00 sec)

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
29
30
31
32
33

§ 3. 语义检查

1. 不能与ANSI连接同时使用。

greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c1 = t2.c1(+);
ERROR 7526 (HY000): OuterJoin: old style outer join (+) cannot be used with ANSI joins

1
2

1. 不能在关联列上指定 (+)。

greatsql> SELECT * FROM t1 WHERE t1.id = (SELECT c1 FROM t2 WHERE t2.c1  = t1.c1(+));
ERROR 7526 (HY000): OuterJoin: an outer join cannot be specified on a correlation column

1
2

1. (+) 列不能外部连接到子查询上。

greatsql> SELECT * FROM t1 WHERE t1.c1(+) = (SELECT id FROM t2);
ERROR 7526 (HY000): OuterJoin: a column may not be outer-joined to a subquery

1
2

1. (+) 操作符只能应用于列，不能应用于任意表达式。但任意表达式可以包含一个或多个用 (+) 操作符标记的列。

-- 合法
greatsql> SELECT * FROM t1, t2 WHERE t1.c1(+) + t1.c1(+) < t2.c1;

-- 合法
greatsql> SELECT * FROM t1, t2 WHERE t1.c1 + t2.c1(+) < t1.c1;

-- 报错
greatsql> SELECT * FROM t1, t2 WHERE t1.c1(+) + t2.c1(+) < t2.c1; 
ERROR 7526 (HY000): OuterJoin: a predicate may reference only one outer-joined table

1
2
3
4
5
6
7
8
9

1. 包含 (+) 操作符的 WHERE 条件不能与其它条件使用 OR 操作符组合。

greatsql> SELECT * FROM t1, t2 WHERE t1.c1+t2.c1(+) < t1.c1 OR t1.c1 = 5;
ERROR 7526 (HY000): OuterJoin: outer join operator (+) not allowed in operand of OR or IN

greatsql> SELECT * FROM t1, t2 WHERE t1.c1(+) in (t2.c1, t2.c1);
ERROR 7526 (HY000): OuterJoin: outer join operator (+) not allowed in operand of OR or IN

-- IN被优化改写为=
greatsql> EXPLAIN FORMAT=JSON SELECT * FROM t1, t2 WHERE t1.c1(+) in (t2.c1);
...
          "attached_condition": "<if>(is_not_null_compl(t1), (`greatsql`.`t1`.`c1` = `greatsql`.`t2`.`c1`), true)"
...

1
2
3
4
5
6
7
8
9
10
11

1. 有几种不能连接的场景

-- 不能自己与自己
greatsql> SELECT * FROM t1 WHERE t1.c1(+) = t1.c1;
ERROR 7526 (HY000): OuterJoin: two tables cannot be outer-joined to each other

-- 两表左右重复连接
greatsql> SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1(+) AND t1.c1(+) = t2.c1;
ERROR 7526 (HY000): OuterJoin: two tables cannot be outer-joined to each other

-- 先自己连接自己，再连接其他表
greatsql> SELECT * FROM t1, t2 WHERE t2.c1 + t2.c1(+) < t1.c1;
ERROR 7526 (HY000): OuterJoin: a table may be outer joined to at most one other table

-- 合法
greatsql> SELECT * FROM t1, t2 WHERE t2.c1(+) + t2.c1(+) < t1.c1;
+----+------+------+------+
| id | c1   | id   | c1   |
+----+------+------+------+
|  1 |    1 | NULL | NULL |
|  2 |    2 | NULL | NULL |
|  3 |    3 |    1 |    1 |
|  4 |    4 |    1 |    1 |
+----+------+------+------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1. 一个表只能最多连接到一个表，不能连接多个表。


greatsql> SELECT * FROM t1, t2, t3 WHERE t1.c1(+) = t2.c1 AND t1.c1(+) = t3.c1;
ERROR 7526 (HY000): OuterJoin: a table may be outer joined to at most one other table

-- (+)所在的另外一侧，涉及多个表
-- Oracle 12c以上版本支持该用法
greatsql> SELECT * FROM t1, t2, t3 WHERE t1.c1(+) = t2.c1+t3.c1;
ERROR 7526 (HY000): OuterJoin: a table may be outer joined to at most one other table

1
2
3
4
5
6
7
8

1. 投影列, CONNECT BY, ORDER BY 等不支持使用 (+) 运算符。

greatsql> SELECT c1(+) FROM t1;
ERROR 7526 (HY000): OuterJoin: outer join operator (+) is not allowed here

1
2

9.以下两种情况，(+)无意义会被可忽略
- a. 如果两表有多个连接条件，则必须为每个关联条件指定 (+)，否则 (+) 没有意义，会被忽略，相当于INNER JOIN。
- b. 当 (+) 关联的表一个在外查询，一个在内查询时，例如：SELECT * FROM t1 WHERE t1.c1 = (SELECT a FROM t2 WHERE t2.c1(+) = t1.c1);。

扫码关注微信公众号

greatsql-wx

§ Oracle兼容-语法-Oracle(+)语法

§ 1. 语法

§ 2. (+)语法使用示例及说明

§ 3. 语义检查

§ 2. `(+)`语法使用示例及说明