Can someone help explain why not using SQL JOIN is bad practice and wrong?

Question

Possible duplicate:
Explicit vs Implicit SQL Joins
SQL JOIN: is there a difference between USING, ON or WHERE?

I am looking at code supported by a developer who is not very well versed in SQL. I often see fragments in my code, such as the following:

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

I suggested that he use the following instead:

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

He pointed out that in this particular case, both queries return identical results in an identical time interval (34k rows in 67 ms). The fact that my new query did not change anything in this case is proof that there is nothing wrong with this method. I tried to explain to him Cartesian products and the like, but he insists that there is nothing wrong with this method. Can someone help provide negative examples of where this might happen and / or why is this query string dangerous from an implementation point of view?

Answer 1

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

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

 SELECT * FROM A, B JOIN C ON Cx = Ay; 

The JOIN statement takes precedence over a comma. Since the above query tries to evaluate Cx = Ay , he does not even know that A is part of the request. This way you get the error message:

 ERROR 1054 (42S22): Unknown column 'Ay' in 'on clause' 

The best way is to use JOIN syntax rather than mixing it.

Also, you cannot create external connections with semicolon syntax. Oracle and Sybase / Microsoft invented their own proprietary syntax for processing external joins, but none of them are supported by other RDBMS brands. Today, all current versions of RDBMS, including Oracle and Sybase / Microsoft, support the standard JOIN syntax, so there is no reason to use legacy vendor-related extensions.

Answer 2

This is just a syntax variant. Both are compound specifications. The first is just implicit. The second is just plain. Early SQL standards did not have the JOIN keyword.

Can anyone help provide negative examples of where to rely on this fail,

No.

and / or why is this query string dangerous from an implementation point of view?

This is not true. This is just the older (but still quite correct) syntax.

Answer 3

I think it is about readability. Consider this:

 SELECT * FROM person INNER JOIN status ON status.id = person.status_id where person.sex=1 or status.state='PA' 

Unlike:

 SELECT * FROM person, status WHERE person.status_id = status.id and person.sex=1 or status.state ='PA'