Doing a left join with old style joins

问题:

So I'm currently transcribing OracleSQL into a new PostgreSQL database. While I'm translating Oracle's (+) joins to LEFT OUTER JOINs, this works very well.

Except when combined with old style joins. Take this OracleSQL for example:

SELECT
    *
FROM
    table1,
    table2 alias1,
    table2 alias2,
    table2 alias3,
    table3,
    table4,
    table5,
    table6 table6alias
WHERE
    table1.id_table3 = alias1.id_table3
    AND table1.id_table4 = alias2.id_table4
    AND table1.id_table3 = table3.id_table3
    AND table1.id_table4 = table4.id_table4
    AND table1.id_table5 = table5.id_table5
    AND table1.table1_id_table3_sender = alias3.id_table3 (+)
    AND alias1.id_svw_uebertragungsweg = table6alias.id_svw
    AND table1.id_table3 != 0
    AND ( table1.id_usr = 0
          OR table1.id_usr IS NULL )

It works very well on Oracle's SQL Developer. So I tried transcribing it like this:

SELECT
    *
FROM
    table1,
    table2 alias1,
    table2 alias2,
    table3,
    table4,
    table5,
    table6 table6alias
LEFT OUTER JOIN table2 alias3
ON table1.table1_id_table3_sender = alias3.id_table3
WHERE
    table1.id_table3 = alias1.id_table3
    AND table1.id_table4 = alias2.id_table4
    AND table1.id_table3 = table3.id_table3
    AND table1.id_table4 = table4.id_table4
    AND table1.id_table5 = table5.id_table5
    AND alias1.id_svw_uebertragungsweg = table6alias.id_svw
    AND table1.id_table3 != 0
    AND ( table1.id_usr = 0
          OR table1.id_usr IS NULL )

Giving me this error:

ORA-00904: "table1"."table1_id_table3_sender": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error on line: 12 row: 8

I've already done only the JOIN, and then it works flawlessly. When adding just one single other JOIN, it gives me this error, that the row can't be found (invalid identifier).

I've already read on the internet that LEFT JOINs don't red more than the first table of the FROM statement. But in this case that would be enough (in my view) - also just adding one other table leads to the error.

So what is my approach to solve this translation to PostgreSQL? Do I have to rewrite ALL of the old style JOINs into newer style ones (which would be very tedious, as there are a ton of these SELECTs) or is there something I just don't get my eyes on?

回答1:

The problem is (not sure if it's standard but most database engines seem to follow it) explicit joins are processed before implicit joins. At the point at which you're doing your join, the only tables/aliases which are in scope are table6alias and alias3. But you're trying to reference table1 in your ON clause for the join.

As you suspected, the solution is to use explicit joins throughout, which also gives you more control over the order in which joins happen.

(Or the quick fix would be to put the LEFT JOIN to alias3 immediately after table1)

回答2:

Don’t mix explicit and implicit JOINs. Matter of fact, just don’t use implicit JOINs.

Here is the query you want :

SELECT
    *
FROM
    table1
    INNER JOIN table2 alias1 ON table1.id_table3 = alias1.id_table3 
    INNER JOIN table2 alias2 ON table1.id_table4 = alias2.id_table4
    INNER JOIN table3 ON table1.id_table3 = table3.id_table3
    INNER JOIN table4 ON table1.id_table4 = table4.id_table4
    INNER JOIN table5 ON table1.id_table5 = table5.id_table5
    INNER JOIN table6 table6alias ON alias1.id_svw_uebertragungsweg = table6alias.id_svw
    LEFT JOIN table2 alias3 ON table1.table1_id_table3_sender = alias3.id_table3
WHERE
    table1.id_table3 != 0
    AND ( table1.id_usr = 0 OR table1.id_usr IS NULL )

回答3:

You are doing incorrect join it should be like this:

SELECT
 *
FROM
 table1 left outer join table2 alias3 on table1.table1_id_table3_sender = 
 alias3.id_table3,
 table2 alias1,
 table2 alias2,
 table3,
 table4,
 table5,
 table6 table6alias
WHERE
table1.id_table3 = alias1.id_table3
AND table1.id_table4 = alias2.id_table4
AND table1.id_table3 = table3.id_table3
AND table1.id_table4 = table4.id_table4
AND table1.id_table5 = table5.id_table5
AND alias1.id_svw_uebertragungsweg = table6alias.id_svw
AND table1.id_table3 != 0
AND ( table1.id_usr = 0
      OR table1.id_usr IS NULL )