Can you please help me? 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
CodePudding user response：
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.
CodePudding user response：
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
CodePudding user response：
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
db<>fiddle here