有人可以帮忙解释一下为什么不使用SQL JOIN是错误的做法吗? [英] Can someone help explain why not using a SQL JOIN is bad practice and wrong?
问题描述
Possible Duplicate:
Explicit vs implicit SQL joins
SQL JOIN: is there a difference between USING, ON or WHERE?
我正在查看由对SQL不太熟悉的开发人员维护的代码.我在他的代码中经常看到如下代码片段:
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
他指出了一个事实，在这种特殊情况下，两个查询在相同的时间范围内(67毫秒内有34,000行)返回了相同的结果.在这种情况下，我的新查询没有任何改变的事实向他证明了这种方法没有错.我曾尝试向他解释笛卡尔乘积，但他坚持认为这种方法没有错.有人可以帮忙提供一些负面示例，说明在何处依赖它会失败，以及/或者为什么从实施的角度来看这行查询很危险?
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?
推荐答案
的确，两种语法形式都应给出相同的结果，并且MySQL在内部以完全相同的方式执行它们.当前版本的SQL标准支持两种形式，尽管仅出于向后兼容的目的而支持逗号样式.
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;
JOIN运算符的优先级高于逗号.因此，当上述查询试图评估C.x = A.y时，它甚至都不知道A是查询的一部分.所以你会得到一个错误:
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'
最好的解决方法是始终使用JOIN语法，而不要混合使用它们.
The best remedy is to use the JOIN syntax consistently instead of mixing them.
此外，您不能使用逗号联接语法进行外部联接. Oracle和Sybase/Microsoft各自发明了自己的专有语法来处理外部联接，但是其他品牌的RDBMS都不支持.如今，包括Oracle和Sybase/Microsoft在内的所有当前RDBMS版本都支持标准的JOIN语法，因此没有充分的理由使用特定于供应商的旧扩展.
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.
这篇关于有人可以帮忙解释一下为什么不使用SQL JOIN是错误的做法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
