A strange Oracle SQL with from 3 tables but only left join 2 in where clause?

Scott Chu Source

I constantly see "from" 2 tables and "join" 2 tables examples. That's why when I see following Oracle SQL statement, I feel strange:

select a.oid, m.acct, p.cdate
from a, p, m
where a.oid = p.oid(+) and a.uid = m.uid
  1. What does it actually happen in DBMS?
  2. What's the equivalent MySQL syntax?
  3. Is JOIN just another way to express some conditions in where clause? e.g. a left join b on a.id=b.id is actually only another way to express a.id = b.id or a.id not in (select b.id from b) in where clause


answered 1 year ago Kacper #1

This is equivalent to:

select a.oid, m.acct, p.cdate
from a left outer join p on (a.oid = p.oid)
inner join m on (a.uid = m.uid)

Query you posted is written in old syntax but does exactly the same as select above. Having simpler example:

select * from a, b where a.id = b.id;

is also equivalent to:

select * from a inner join b on (a.id = b.id);

Both queries should be processed same way by optimizer. Also join order is determined by optimizer. So getting back to original example optimizer will define if first apply inner join and then left join or opposite.

comments powered by Disqus