将Oracle旧式外部联接转换为Ansi SQL [英] Convert Oracle legacy outer join to Ansi SQL

问题描述

我有一个复杂的非ansi查询，我需要在ansi查询中进行转换.

I have a complex non ansi query and I need to convert in ansi query.

所以我将举一个小例子来描述我的问题

so I will take a small example to describe my problem

输入查询

 SELECT a.name,
         a.empno,
         b.loc,
         c.inr
    FROM a,
         b,
         c
   WHERE a.deptno = b.deptno(+)
     AND b.empno(+) = 190
     a.deptno = c.deptno(+)
     AND c.empno(+) = 190;

预先感谢

推荐答案

您的查询等同于以下符合ANSI的查询:

Your query is equivalent to the below ANSI compliant query:

SELECT a.name,
       a.empno,
       b.loc,
       c.inr
FROM tab a
LEFT JOIN tab b ON a.deptno = b.deptno AND b.empno = 190
LEFT JOIN tab c ON a.deptno = c.deptno AND c.empno = 190;         

您必须将谓词b.empno = 190和c.empno = 190放在LEFT JOIN操作的ON子句中，否则LEFT JOIN变为INNER JOIN.

You have to place predicates b.empno = 190 and c.empno = 190 inside the ON clauses of the LEFT JOIN operations, otherwise LEFT JOIN becomes an INNER JOIN.

这篇关于将Oracle旧式外部联接转换为Ansi SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！