CODEWITHSUNDEEP
mysqloracle-databaseleft-join
Scott Chu
Scott Chu

Reputation: 990

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

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

Upvotes: 0

Views: 108

Answers (1)

Kacper
Kacper

Reputation: 4818

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.

Upvotes: 2

Related Questions