1

I need to convert the following Oracle SQL to ANSI SQL.

Select t1.c1, t2.c2, t1.c3 from t1, t2 where
T1.c1=t2.c1(+) and
T1.c2=t2.c2(+) and
T1.c3=t2.c3 and
T1.c4=t2.c4 and
T1.c1='1'
philipxy
  • 14,867
  • 6
  • 39
  • 83
Shubham Shukla
  • 19
  • 2
  • This is a faq. What does *the manual* say about (+)? What do the zillions of SO answers say? PS (Per your comment on an answer:) Read a *definition* of left join on: it returns inner join on rows plus unmatched left table rows extended by nulls. – philipxy Apr 01 '18 at 20:09
  • Does this answer your question? [Oracle "(+)" Operator](https://stackoverflow.com/questions/4020786/oracle-operator) – philipxy Dec 25 '22 at 06:44

1 Answers1

3

This would be outer join if all columns in t2 had the (+) modifier.

That would look like:

Select t1.c1, t2.c2, t1.c3
from t1 left join
     t2 
     on T1.c1 = t2.c1 and T1.c2 = t2.c2 and
        T1.c3 = t2.c3 and T1.c4 = t2.c4
where T1.c1 = '1';

However, your version is an inner join, because some of the columns do need to match -- so there needs to be a matching row in the second table.

So, the real equivalent is just:

Select t1.c1, t2.c2, t1.c3
from t1 join
     t2 
     on T1.c1 = t2.c1 and T1.c2 = t2.c2 and
        T1.c3 = t2.c3 and T1.c4 = t2.c4
where T1.c1 = '1';

And the (+) is not relevant.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Could you explain what the behavior is if only _some_ of the join conditions bear a `+` operator (I don't know this) ? – Tim Biegeleisen May 16 '17 at 12:30
  • 2
    @TimBiegeleisen . . . Ignore the `(+)` in that case. The other columns need to match, which undoes the outer join. By the way, your answer is technically correct, but the `WHERE` clause turns the outer join to an inner join. – Gordon Linoff May 16 '17 at 12:31
  • Will the final answer contains rows for which c1 and c2 are null and c3 and c4 matched for t2 since for c1 and c2 left outer join is used? – Shubham Shukla May 16 '17 at 14:11
  • 1
    @ShubhamShukla . . . The `(+)` operation (as far as I know) is not about matching `NULL` values to `NULL` values. It is about adding new rows when there is not match in the other table. – Gordon Linoff May 17 '17 at 02:34