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

1. 语法

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

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

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

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

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

6. greatsql> SELECT * FROM t1;
7. +----+------+
8. | id | c1   |
9. +----+------+
10. |  1 |    1 |
11. |  2 |    2 |
12. |  3 |    3 |
13. |  4 |    4 |
14. +----+------+
15. 4 rows in set (0.00 sec)

17. greatsql> SELECT * FROM t2;
18. +----+------+
19. | id | c1   |
20. +----+------+
21. |  4 |    4 |
22. |  3 |    3 |
23. |  2 |    2 |
24. |  1 |    1 |
25. +----+------+
26. 4 rows in set (0.00 sec)

28. greatsql> SELECT * FROM t1;
29. +----+------+
30. | id | c1   |
31. +----+------+
32. |  1 |    1 |
33. |  2 |    2 |
34. |  3 |    3 |
35. |  4 |    4 |
36. +----+------+
37. 4 rows in set (0.00 sec)
    1. 支持多种用法。
2. -- `(+)` 写在被连接表一侧，表示 t1 LEFT JOIN t2
3. greatsql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1(+);
4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
5. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
7. |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
8. |  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (hash join) |
9. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
10. 2 rows in set, 1 warning (0.00 sec)

12. -- 可以看到是t1 left join t2
13. greatsql> SHOW WARNINGS;
14. *************************** 1. row ***************************
15. Level: Note
16. Code: 1003
17. 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

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

29. -- 可以看到是t2 left join t1
30. greatsql> SHOW WARNINGS;
31. *************************** 1. row ***************************
32. Level: Note
33. Code: 1003
34. 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

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

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

51. greatsql> SHOW WARNINGS\G
52. *************************** 1. row ***************************
53. Level: Note
54. Code: 1003
55. 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

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

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

73. greatsql> SHOW WARNINGS\G
74. *************************** 1. row ***************************
75. Level: Note
76. Code: 1003
77. 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. (+) 只能用在单列上，不能用于表达式或者常量。
1. -- 错误示例，报告语法错误
2. greatsql> SELECT * FROM t1, t2 WHERE t1.c1 = 0(+);
3. 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

5. greatsql> SELECT * FROM t1, t2 WHERE (t1.c1 + t2.c1)(+) = t2.id;
6. 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. (+) 只能用于 WHERE 表达式中的列，用在其它位置的列会忽略其作用。
1. -- 下面的示例不会按照t2.c1列排序或分组
2. greatsql> SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 ORDER BY t2.c1(+);
3. +----+------+----+------+
4. | id | c1   | id | c1   |
5. +----+------+----+------+
6. |  1 |    1 |  1 |    1 |
7. |  2 |    2 |  2 |    2 |
8. |  3 |    3 |  3 |    3 |
9. |  4 |    4 |  4 |    4 |
10. +----+------+----+------+
11. 4 rows in set (0.00 sec)

13. greatsql> SELECT t2.c1 FROM t1, t2 WHERE t1.c1 = t2.c1 GROUP BY t2.c1(+);
14. +------+
15. | c1   |
16. +------+
17. |    4 |
18. |    3 |
19. |    2 |
20. |    1 |
21. +------+
22. 4 rows in set (0.00 sec)

24. greatsql> SELECT t1.c1, t2.c1 FROM t1, t2 GROUP BY t2.c1, t1.c1 HAVING t2.c1 = t1.c1(+);
25. +------+------+
26. | c1   | c1   |
27. +------+------+
28. |    4 |    4 |
29. |    3 |    3 |
30. |    2 |    2 |
31. |    1 |    1 |
32. +------+------+
33. 4 rows in set (0.00 sec)

3. 语义检查

    1. 不能与ANSI连接同时使用。
1. greatsql> SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c1 = t2.c1(+);
2. ERROR 7526 (HY000): OuterJoin: old style outer join (+) cannot be used with ANSI joins
    1. 不能在关联列上指定 (+)。
1. greatsql> SELECT * FROM t1 WHERE t1.id = (SELECT c1 FROM t2 WHERE t2.c1  = t1.c1(+));
2. ERROR 7526 (HY000): OuterJoin: an outer join cannot be specified on a correlation column
    1. (+) 列不能外部连接到子查询上。
1. greatsql> SELECT * FROM t1 WHERE t1.c1(+) = (SELECT id FROM t2);
2. ERROR 7526 (HY000): OuterJoin: a column may not be outer-joined to a subquery
    1. (+) 操作符只能应用于列，不能应用于任意表达式。但任意表达式可以包含一个或多个用 (+) 操作符标记的列。
1. -- 合法
2. greatsql> SELECT * FROM t1, t2 WHERE t1.c1(+) + t1.c1(+) < t2.c1;

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

7. -- 报错
8. greatsql> SELECT * FROM t1, t2 WHERE t1.c1(+) + t2.c1(+) < t2.c1;
9. ERROR 7526 (HY000): OuterJoin: a predicate may reference only one outer-joined table
    1. 包含 (+) 操作符的 WHERE 条件不能与其它条件使用 OR 操作符组合。
1. greatsql> SELECT * FROM t1, t2 WHERE t1.c1+t2.c1(+) < t1.c1 OR t1.c1 = 5;
2. ERROR 7526 (HY000): OuterJoin: outer join operator (+) not allowed in operand of OR or IN

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

7. -- IN被优化改写为=
8. greatsql> EXPLAIN FORMAT=JSON SELECT * FROM t1, t2 WHERE t1.c1(+) in (t2.c1);
9. ...
10. "attached_condition": "<if>(is_not_null_compl(t1), (`greatsql`.`t1`.`c1` = `greatsql`.`t2`.`c1`), true)"
11. ...
    1. 有几种不能连接的场景
1. -- 不能自己与自己
2. greatsql> SELECT * FROM t1 WHERE t1.c1(+) = t1.c1;
3. ERROR 7526 (HY000): OuterJoin: two tables cannot be outer-joined to each other

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

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

13. -- 合法
14. greatsql> SELECT * FROM t1, t2 WHERE t2.c1(+) + t2.c1(+) < t1.c1;
15. +----+------+------+------+
16. | id | c1   | id   | c1   |
17. +----+------+------+------+
18. |  1 |    1 | NULL | NULL |
19. |  2 |    2 | NULL | NULL |
20. |  3 |    3 |    1 |    1 |
21. |  4 |    4 |    1 |    1 |
22. +----+------+------+------+
23. 4 rows in set (0.00 sec)
    1. 一个表只能最多连接到一个表，不能连接多个表。
2. greatsql> SELECT * FROM t1, t2, t3 WHERE t1.c1(+) = t2.c1 AND t1.c1(+) = t3.c1;
3. ERROR 7526 (HY000): OuterJoin: a table may be outer joined to at most one other table

5. -- (+)所在的另外一侧，涉及多个表
6. -- Oracle 12c以上版本支持该用法
7. greatsql> SELECT * FROM t1, t2, t3 WHERE t1.c1(+) = t2.c1+t3.c1;
8. ERROR 7526 (HY000): OuterJoin: a table may be outer joined to at most one other table
    1. 投影列, CONNECT BY, ORDER BY 等不支持使用 (+) 运算符。
1. greatsql> SELECT c1(+) FROM t1;
2. ERROR 7526 (HY000): OuterJoin: outer join operator (+) is not allowed here
  • 9.以下两种情况，(+)无意义会被可忽略
  • a. 如果两表有多个连接条件，则必须为每个关联条件指定 (+)，否则 (+) 没有意义，会被忽略，相当于INNER JOIN。
  • b. 当 (+) 关联的表一个在外查询，一个在内查询时，例如：SELECT * FROM t1 WHERE t1.c1 = (SELECT a FROM t2 WHERE t2.c1(+) = t1.c1);。

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx