Why table may be outer joined to at most one other table ?

general
, ,
1

Here is a question which has been boggling me for few days now, and I searched and searched but couldn’t find any convincing answer !

Simple question, why is it restricted to have 2 Outer Joins in SQL, on same table even with different columns being used, check the queries below for better understanding. Also I can overcome them using nested sub query or ANSI joins, but then why it is even restricted in the first place using (+) operator!

In this question I’m referring to the error : “ORA-01417: a table may be outer joined to at most one other table”

What I want to ask is :

Why this is allowed :

select * from
a, b, c
where a.a1 = b.b1
and a.a2 = c.c1

And why this is not allowed:

select * from
a, b, c
where a.a1(+) = b.b1
and a.a2(+) = c.c1

Please leave ANSI and Nested SubQueries alone :slight_smile:

2

I think logically Joins concept is designed by considering 2 tables/objects. I couldn’t imagine three tables as a test case here because full outer join is the case where first table is joined to second after matching condition(doesn’t really matter here as it first performs inner join first and then left outer and right outer). After this action is performed the mapping is considered as one unit and your third table as another unit which is achieved with sub queries.

In my view, the example query provided doesn’t perform the outer join as it will try to fetch you the results only after “where a.a1 = b.b1 and a.a2 = c.c1” clause/condition is met. So, you were actually trying to do inner join here.

3

I think, you just do not understand properly the old syntax (which is why I’m not using it)…

The ANSI syntax (what you really wanted) is probably

select *
  from a
  left join b on b.b1 = a.a1
  left join c on c.c1 = a.a2

right?

What you wrote is similar to:

select *
  from b, c
  left join a on a.a1 = b.b1
  left join a on a.a2 = c.c1

Which is obviously incorrect, isn’t it?

SO is probably better place to ask such questions. Related question is here.

The proper old syntax should be

select *
  from a, b, c
 where a.a1 = b.b1 (+)
   and a.a2 = c.c1 (+)

See the SQL fiddle example.

4

select * from a, b, c where a.a1 = b.b1 and a.a2 = c.c1
IS CORRECT