CODEWITHSUNDEEP
sqloracle-databasejoin
user3181672
user3181672

Reputation: 119

Outer join in oracle with ANSI standard

select ...
from A left outer join B on (B.x=A.x)
left outer join C on (C.y=A.y)

want to add one additional join of table D with table C with a condition D.z=C.z

select ...
from A left outer join B on (B.x=A.x)
left outer join C on (C.y=A.y), D inner join C on (D.z=C.z)

however, query does not work after adding this part " , D inner join C on (D.z=C.z) ".

Any suggestions ?

Upvotes: 2

Views: 194

Answers (2)

Andriy M
Andriy M

Reputation: 77737

My understanding is that it is not just table C but the result of an inner join between C and D that you want to outer-join to table A.

If that is so, then @valex's suggestion is an alternative but equivalent way to represent that logic.

In some SQL products the syntax would allow you to write out the logic exactly as intended:

…
FROM
  A
  LEFT JOIN B ON (B.x=A.x)
  LEFT JOIN
    C
    INNER JOIN D ON (D.z=C.z)
  ON (C.y=A.y)

Oracle doesn't support such syntax. But you could rewrite the query like this in order to make the syntax more closely reflect the intended logic:

…
FROM
  C
  INNER JOIN D ON (D.z=C.z)
  RIGHT JOIN A ON (C.y=A.y)
  LEFT  JOIN B ON (B.x=A.x)

Now it is clear that C and D are supposed to be inner-joined and their result should be outer-joined to A (A being on the outer side of the join, which is the right side this time, hence RIGHT JOIN), followed by an outer join of B to A.

Still, as I said, @valex's is an equivalent suggestion that should produce the same results.

Upvotes: 0

valex
valex

Reputation: 24144

You should just add left outer join D on (D.z=C.z). If you use INNER JOIN you remove rows from A and B which not connected with C and D

select ...
from A left outer join B on (B.x=A.x)
left outer join C on (C.y=A.y)
left outer join D on (D.z=C.z)

Upvotes: 4

Related Questions