Conditional Outer Joins

In the previous post we saw how to convert an outer join with no join condition from ANSI syntax to Oracle syntax.
In this post we’ll do the same for outer joins with conditional join conditions.

In the following query the join includes a filter condition on t2:

select *
from t1
left outer join t2
  on (t2.t1_id = t1.id and t2.x = :x);

Converting this query to the Oracle syntax is straightforward:

select *
from t1, t2
where t2.t1_id(+) = t1.id
and t2.x(+) = :x;

In the following query the join includes a filter condition on t1:

select *
from t1
left outer join t2
  on (t2.t1_id = t1.id and t1.a = :a);

Converting this query to the Oracle syntax is a bit more tricky. Here is one option how to do it:

select *
from t1, t2
where t2.t1_id(+) = decode(t1.a, :a, t1.id);