Joining tables in Oracle (multiple outer joins)

Question

I am trying to convert an Informix query to Oracle:

The Informix query looks like this:

SELECT
    r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr 
FROM
    tab1 u, tab2 ud, 
OUTER (tab3 a, tab4 n, tab5 nd, tab6 r, OUTER (tab7 g, tab8 atr))
WHERE
    r.xx = n.xx AND
    n.nas = a.nas AND
    a.user = u.user AND
    a.ac = g.ac AND
    n.nas1 = nd.nas1 AND
    u.user1 = ud.user1 AND
    atr.sso = g.sso AND
    UPPER(atr.name) = 'NAME' AND
    u.id = 102 

The Oracle query looks like this:

SELECT
    r.aa, n.bb, nd.cc,u.id, ud.dd, g.attr
FROM
    tab1 u
INNER JOIN tab2 ud ON
    u.user1 = ud.user1 AND
    u.id = 102
LEFT OUTER JOIN tab3 a ON a.user = u.user 
LEFT OUTER JOIN tab4 n ON n.nas = a.nas
LEFT OUTER JOIN tab5 nd ON  n.nas1 = nd.nas1 
LEFT OUTER JOIN tab6 r ON r.xx = n.xx

I am not sure how to join the other two tables.

Can any one help me?

Answer 1

I believe the query should look something like this:

SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
  FROM            tab1 AS u
       INNER JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
  LEFT OUTER JOIN tab3 AS a ON a.user  = u.user 
  LEFT OUTER JOIN tab4 AS n ON n.nas   = a.nas
  LEFT OUTER JOIN tab5 AS d ON n.nas1  = d.nas1 
  LEFT OUTER JOIN tab6 AS r ON r.xx    = n.xx
  LEFT OUTER JOIN (SELECT g.attr, g.ac
                     FROM tab7 AS x
                     JOIN tab8 AS atr ON x.sso = atr.sso
                    WHERE UPPER(atr.name) = 'NAME'
                  )    AS g ON a.ac    = g.ac

I changed the alias 'nd' to just 'd' and 'ud' to 'v' so that all aliases are single-letters. The nested OUTER(tab7 g, tab8 atr) in the Informix notation is itself an inner join (as in the sub-select in my version), but that result set is outer joined with a.ac. This is what the rewrite says.

I used a WHERE clause in the sub-query; the WHERE condition could be left in the ON clause if you preferred. The chances are the optimizer will handle both correctly and equivalently. Similarly, the AND u.id = 102 in the inner join could be placed into a WHERE clause. Again, the optimizer would probably push the filter condition down for better performance.

Note that the UPPER function in the sub-query likely requires a table-scan - unless you have a functional index on UPPER(atr.name).

---

Revisiting this, the transliteration of the initial part of the query is not accurate.

The original query included the FROM clause:

FROM tab1 u, tab2 ud, OUTER(tab3 a, tab4 n, tab5 nd, tab6 r, OUTER(tab7 g, tab8 atr))

The tables tab3, tab4, tab5 and tab6 are inner-joined to each other, and the result is outer-joined to tab1 and tab2. Similarly, tab8 is inner-joined to tab7, but the result of that is outer-joined to the inner-join of tables 3-6. The original answer I gave (based on the outline answer in the question) would be represented in the old Informix notation using:

FROM tab1 u, tab2 ud,
     OUTER(tab3 a, OUTER(tab4 n, OUTER(tab5 nd, OUTER(tab6 r, OUTER(tab7 g, tab8 atr)))))

Thus, it would be more accurate to transcribe the original query as:

SELECT r.aa, n.bb, nd.cc, u.id, ud.dd, g.attr
  FROM tab1 AS u
  JOIN tab2 AS v ON u.user1 = v.user1 AND u.id = 102
  LEFT OUTER JOIN
       (SELECT *
          FROM tab3 AS a ON a.user  = u.user 
          JOIN tab4 AS n ON n.nas   = a.nas
          JOIN tab5 AS d ON n.nas1  = d.nas1 
          JOIN tab6 AS r ON r.xx    = n.xx
          LEFT OUTER JOIN
               (SELECT g.attr, g.ac
                 FROM tab7 AS x
                 JOIN tab8 AS atr ON x.sso = atr.sso
                WHERE UPPER(atr.name) = 'NAME'
               )    AS g ON a.ac    = g.ac
       ) AS loj

The residual problem would be ensuring that the correct aliases are in use for the columns from the complex loj sub-query. Note that in the absence of LEFT, RIGHT or FULL, a JOIN is assumed to be an INNER join; also, if you specify LEFT, RIGHT or FULL, the OUTER is optional.

One other detail to note: the behaviour of the old-style Informix OUTER join under filter conditions is not the same as the behaviour of Standard SQL OUTER joins. This seldom makes a difference, but it could occasionally be important. On the whole, the behaviour of Standard SQL OUTER joins is more usually what you want, but you if you run regression tests and find that there's a difference in the answers, the explanation might be that the old-style Informix OUTER join does things differently from the new-style Standard SQL OUTER joins.