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