I've been using Oracle 9 at work a lot recently which has opened my eyes to the ANSI standard JOIN syntax and the clarity that it brings to query statements. At home I use MySQL and since this also supports the ANSI standards I have migrated to using the same format there too. Having seen the light I now can't see myself going back to the old style.

Supposing we wanted to know which customers had placed orders for items over £100. If I were to code a query previously I would quite happily have used the following syntax.

SELECT Customer.Name, Order.Product, Order.Price
FROM Customer, Order
WHERE Customer.ID=Order.CustomerID
AND Order.Price > 100;

In this very rudimentary example there is nothing wrong with the query but if we start joining on many tables the conditionals in the WHERE clause can become quite extensive. This is compounded when we start using LEFT and RIGHT OUTER JOINs (I personally find Oracle 8's outer join syntax unintuitive).

Furthermore we're not making any distinction between conditionals that define the structure of the schema with conditionals that narrow the query. I particularly like the new syntax because it achieves this separation. It might be more long-winded but on complex queries it certainly adds to readability.

In the new syntax this would be written as

SELECT Customer.Name, Order.Product, Order.Price
FROM Customer
INNER JOIN Order ON Customer.ID=Order.CustomerID
WHERE Order.Price > 100;