How to convert from old Oracle join type to ANSI joins and why?
SELECT *
FROM a, b, c
WHERE b.id (+) = a.id1
AND b.xxx = c.yyy
AND c.id (+) = a.id2
--Should be this 1?
select * from
A
left outer join B on B.ID = A.ID1
left outer join C on C.ID = A.ID2 AND B.xxx = C.yyy
--or this 2?
select * from
A
left outer join C on C.ID = A.ID2
left outer join B on B.ID = A.ID1 AND B.xxx = C.yyy
You can also cheat by using Oracle SQL Developer.
Paste in your ANSI SQL query or Oracle SQL Query, select it, right click, and use the convert feature.
The SQL parser will rewrite the JOINS for you.
So this would be for any query, not just the one you have in your question/scenario.
You should of course check the execution plans and data returned by the queries to make sure they are functionally equivalent.
According to the Oracle documentation:
If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.
So there is an inner join between b and c. And because of the overall conditions, this is going to turn all the joins into INNER JOIN (there needs to be valid values in b and c for that condition to work.
I think the equivalent logic is:
SELECT *
FROM a JOIN
b
ON b.id = a.id1 JOIN
c
ON c.id = a.id2 AND b.xxx = c.yyyy;
That is, the simple equality eliminates turns the outer joins into inner joins.
Of course, you can test this.
Neither of your options:
select *
from A
left outer join B on B.ID = A.ID1
left outer join C on C.ID = A.ID2 AND B.xxx = C.yyy
Would be written as:
SELECT *
FROM a, b, c
WHERE b.id (+) = a.id1
AND b.xxx = c.yyy (+)
AND c.id (+) = a.id2
and:
select *
from A
left outer join C on C.ID = A.ID2
left outer join B on B.ID = A.ID1 AND B.xxx = C.yyy
Would be written as:
SELECT *
FROM a, b, c
WHERE b.id (+) = a.id1
AND b.xxx (+) = c.yyy
AND c.id (+) = a.id2
What you have is:
SELECT *
FROM a
INNER JOIN b ON (a.id1 = b.id)
INNER JOIN c ON (a.id2 = c.id AND b.xxx = c.yyy)
why?
SELECT *
FROM a, b, c
WHERE b.id (+) = a.id1
AND b.xxx = c.yyy
AND c.id (+) = a.id2
The line:
AND b.xxx = c.yyy
Requires that there is a b and a c row; this will not occur when there is a left-outer join so the join is the equivalent of an inner join and the query could be rewritten as:
SELECT *
FROM a, b, c
WHERE b.id = a.id1
AND b.xxx = c.yyy
AND c.id = a.id2
And all then it is clearer that all the joins are inner joins.
What you may have intended to write was:
select *
from A,
(
SELECT b.id AS b_id,
c.id AS c_id,
b.xxx,
c.yyy
FROM b, c
WHERE b.xxx = c.yyy
) bc
WHERE bc.b_id (+) = a.id1
AND bc.c_id (+) = a.id2
Which would be:
select *
from A
left outer join (
SELECT b.id AS b_id,
c.id AS c_id,
b.xxx,
c.yyy
FROM b
INNER JOIN c ON b.xxx = c.yyy
) bc
on bc.b_id = a.id1 AND bc.c_id = a.id2
or, using parentheses in the join to set the precedence of the joins:
SELECT *
FROM a
LEFT OUTER JOIN (
b
INNER JOIN c
ON b.xxx = c.yyy
)
ON b.id = a.id1 AND c.id = a.id2
db<>fiddle here
If you run simple explain plan and check the result, you may see that it generates two inner joins:
explain plan set statement_id = 'test' for SELECT * FROM a, b, c WHERE b.id (+) = a.id1 AND b.xxx = c.yyy AND c.id (+) = a.id2
select * from table(dbms_xplan.display(null, 'test'))| PLAN_TABLE_OUTPUT | | :--------------------------------------------------------------------------- | | Plan hash value: 1502482080 | | | | ---------------------------------------------------------------------------- | | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | | ---------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | 1 | 78 | 6 (0)| 00:00:01 | | | |* 1 | HASH JOIN | | 1 | 78 | 6 (0)| 00:00:01 | | | |* 2 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 | | | | 3 | TABLE ACCESS FULL| A | 1 | 26 | 2 (0)| 00:00:01 | | | | 4 | TABLE ACCESS FULL| B | 1 | 26 | 2 (0)| 00:00:01 | | | | 5 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 | | | ---------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - access("B"."XXX"="C"."YYY" AND "C"."ID"="A"."ID2") | | 2 - access("B"."ID"="A"."ID1") | | | | Note | | ----- | | - dynamic statistics used: dynamic sampling (level=2) |
explain plan set statement_id = 'test1' for select * from a left join b on b.id = a.id1 left join c on c.id = a.id2 and b.xxx = c.yyy
select * from table(dbms_xplan.display(null, 'test1'))| PLAN_TABLE_OUTPUT | | :--------------------------------------------------------------------------- | | Plan hash value: 2316364204 | | | | ---------------------------------------------------------------------------- | | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | | ---------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | 1 | 78 | 6 (0)| 00:00:01 | | | |* 1 | HASH JOIN OUTER | | 1 | 78 | 6 (0)| 00:00:01 | | | |* 2 | HASH JOIN OUTER | | 1 | 52 | 4 (0)| 00:00:01 | | | | 3 | TABLE ACCESS FULL| A | 1 | 26 | 2 (0)| 00:00:01 | | | | 4 | TABLE ACCESS FULL| B | 1 | 26 | 2 (0)| 00:00:01 | | | | 5 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 | | | ---------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - access("C"."ID"(+)="A"."ID2" AND "B"."XXX"="C"."YYY"(+)) | | 2 - access("B"."ID"(+)="A"."ID1") | | | | Note | | ----- | | - dynamic statistics used: dynamic sampling (level=2) |
db<>fiddle here
