Why use a JOIN clause versus a WHERE condition?

Question

I develop against Oracle databases. When I need to manually write (not use an ORM like hibernate), I use a WHERE condition instead of a JOIN.

for example (this is simplistic just to illustrate the style):

Select *
from customers c, invoices i, shipment_info si
where c.customer_id = i.customer_id
    and i.amount > 999.99 
    and i.invoice_id = si.invoice_id(+)  -- added to show a replacement for a join
order by i.amount, c.name

I learned this style from an OLD oracle DBA. I have since learned that this is not standard SQL syntax. Other than being non-standard and much less database portable, are there any other repercussions to using this format?

Answer 1

I don't like the style because it makes it harder to determine which WHERE clauses are for simulating JOINs and which ones are for actual filters, and I don't like code that makes it unnecessarily difficult to determine the original intent of the programmer.

Answer 2

The biggest issue that I have run into with this format is the tendency to forget some join's WHERE clause, thereby resulting in a cartesian product. This is particularly common (for me, at least) when adding a new table to the query. For example, suppose an ADDRESSES table is thrown into the mix and your mind is a bit forgetful:

SELECT *
  FROM customers c, invoices i, addresses a
 WHERE c.customer_id = i.customer_id
   AND i.amount > 999.99
 ORDER BY i.amount, c.name

Boom! Cartesian product! :)