Why use a JOIN clause versus a WHERE condition?

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.

Some people will say that this style is less readable, but that's a matter of habit. From a performance point of view, it doesn't matter, since the query optimizer takes care of that.

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! :)

The old style join is flat out wrong in some cases (outer joins are the culprit). Although they are more or less equivalent when using inner joins, they can generate incorrect results with outer joins, especially if columns on the outer side can be null. This is because when using the older syntax the join conditions are not logically evaluated until the entire result set has been constructed, it is simply not possible to express a condition on a column from outer side of a join that will filter records when the column can be null because there is no matching record.

As an example:

Select all Customers, and the sum of the sales of Widgets on all their Invoices in the month Of August, where the Invoice has been processed (Invoice.ProcessDate is Not Null)

using new ANSI-92 Join syntax

 Select c.name, Sum(d.Amount)
 From customer c
    Left Join Invoice I 
        On i.custId = c.custId
            And i.SalesDate Between '8/1/2009' 
                      and '8/31/2009 23:59:59'
            And i.ProcessDate Is Not Null
    Left Join InvoiceDetails d 
        On d.InvoiceId = i.InvoiceId
            And d.Product = 'widget'
 Group By c.Name

Try doing this with old syntax... Because when using the old style syntax, all the conditions in the where clause are evaluated/applied BEFORE the 'outer' rows are added back in, All the UnProcessed Invoice rows will get added back into the final result set... So this is not possible with old syntax - anything that attempts to filter out the invoices with null Processed Dates will eliminate customers... the only alternative is to use a correlated subquery.