Sql join notation: is this equivalent to internal or full join?

Question

select a.somefield, b.someotherfield from a, b where a.key = b.key 

Is the above equivalent:

  select a.somefield, b.someotherfield from a join b on a.key = b.key 

or

  select a.somefield, b.someotherfield from a full join b on a.key = b.key 

I am pretty sure that this is equivalent to an inner join, but just realized that I had never thought about this before. I also wonder if there are any boundary cases where it is not 100% equivalent?

Answer 1

The original concept is equivalent to inner union.

Using the where syntax, there is no standard equivalent for any of the outer joins (left outer join, right outer join, full outer join). Oracle supports the "+" syntax, representing left and right outer joins.

In addition, conditional exception in the where clause is equivalent to cross join.

However, you should be used to using join syntax in the from clause. Once you get used to it, it is much clearer about the intentions of the request and much less error prone.