Can someone help explain why not using a SQL JOIN is bad practice and wrong? [duplicate]

Question

Possible Duplicate:
Explicit vs implicit SQL joins
SQL JOIN: is there a difference between USING, ON or WHERE?

I'm going over code maintained by a developer who was not very familiar with SQL. I see snippets such as the following quite frequently in his code:

SELECT *
FROM person, status
WHERE person.status_id = status.id

I've suggested to him that he use the following instead:

SELECT *
FROM person
INNER JOIN status ON status.id = person.status_id

He pointed to the fact that, in this particular case, both queries returned identical results in an identical time frame (34k rows in 67 ms). The fact that my new query didn't change anything in this case is evidence to him that there is nothing wrong with this method. I've tried explaining cartesian products and such to him, but he insists that there is nothing wrong with this method. Can someone help provide negative examples of where relying on this would fail, and/or why this line of querying is dangerous from an implementation perspective?

Answer 1

It's true that both forms of syntax should give the same result and internally MySQL executes them both in exactly the same way. Current versions of the SQL standard support both forms, although the comma-style is supported only for the sake of backward compatibility.

There is a case where using the comma-style syntax fails, but it's exotic:

SELECT * FROM A, B JOIN C ON C.x = A.y;

The JOIN operator has higher precedence than the comma. So as the above query is trying to evaluate C.x = A.y it doesn't even know that A is part of the query. So you get an error:

ERROR 1054 (42S22): Unknown column 'A.y' in 'on clause'

The best remedy is to use the JOIN syntax consistently instead of mixing them.

Also you can't make outer joins with the comma-join syntax. Oracle and Sybase/Microsoft each invented their own proprietary syntax for handling outer joins, but neither are supported by other brands of RDBMS. Today, all current versions of RDBMS including Oracle and Sybase/Microsoft support the standard JOIN syntax, so there's no good reason to use the legacy vendor-specific extensions.