Oracle(旧?)加入 - 用于转换的工具/脚本?

2021-12-05 00:00:00 sql database oracle plsql outer-join

我一直在移植 oracle selects,我遇到了很多这样的查询:

I have been porting oracle selects, and I have been running across a lot of queries like so:

SELECT e.last_name,
       d.department_name
  FROM employees e,
      departments d
WHERE e.department_id(+) = d.department_id;

...和:

SELECT last_name, 
       d.department_id
  FROM employees e, 
       departments d
 WHERE e.department_id = d.department_id(+);

是否有任何指南/教程可以转换 (+) 语法的所有变体?那个语法叫什么(所以我可以搜索谷歌)?

Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)?

更好.. 有没有工具/脚本可以为我进行这种转换(首选免费)?某种优化器?我有大约 500 个这样的查询要移植..

Even better.. Is there a tool/script that will do this conversion for me (Preferred Free)? An optimizer of some sort? I have around 500 of these queries to port..

这个标准是什么时候淘汰的?任何信息表示赞赏.

When was this standard phased out? Any info is appreciated.

推荐答案

(+) 是 Oracle 特定的 pre-ANSI-92 OUTER JOIN 语法,因为 ANSI-89 语法不提供语法用于 OUTER JOIN 支持.

The (+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn't provide syntax for OUTER JOIN support.

RIGHT还是LEFT由哪个表决定附有符号的列引用.如果它在与 FROM 子句中的第一个表相关联的列旁边指定 - 它是一个 RIGHT 连接.否则,它是一个 LEFT 连接.对于任何需要了解的人来说,这是一个很好的参考JOIN 之间的区别.

Whether it is RIGHT or LEFT is determined by which table & column reference the notation is attached to. If it is specified next to a column associated with the first table in the FROM clause - it's a RIGHT join. Otherwise, it's a LEFT join. This a good reference for anyone needing to know the difference between JOINs.

使用 ANSI-92 语法重写的第一个查询:

First query re-written using ANSI-92 syntax:

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

使用 ANSI-92 语法重写的第二个查询:

Second query re-written using ANSI-92 syntax:

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

相关文章