问题描述
似乎要组合两个或多个表，我们可以使用join或where。
It seems like to combine two or more tables, we can either use join or where. What are the advantages of one over the other?
推荐答案
任何涉及多个表的查询都需要某种形式的关联来链接结果从表A到表B。这样做的传统（ANSI-89）方法是：
Any query involving more than one table requires some form of association to link the results from table "A" to table "B". The traditional (ANSI-89) means of doing this is to:
- 列出FROM子句中的逗号分隔列表中涉及的表
-
在WHERE子句中写入表之间的关联
- List the tables involved in a comma separated list in the FROM clause
Write the association between the tables in the WHERE clause
SELECT *
FROM TABLE_A a,
TABLE_B b
WHERE a.id = b.id
以下是使用ANSI-92 JOIN语法重写的查询：
Here's the query re-written using ANSI-92 JOIN syntax:
SELECT *
FROM TABLE_A a
JOIN TABLE_B b ON b.id = a.id
b $ b
从性能角度：
支持的位置（Oracle 9i +，PostgreSQL 7.2+，MySQL 3.23+ ，SQL Server 2000+），使用其中任一语法都没有性能优势。优化器将它们视为同一查询。但是更复杂的查询可以从使用ANSI-92语法中受益：
From a Performance Perspective:
Where supported (Oracle 9i+, PostgreSQL 7.2+, MySQL 3.23+, SQL Server 2000+), there is no performance benefit to using either syntax over the other. The optimizer sees them as the same query. But more complex queries can benefit from using ANSI-92 syntax:
- 能够控制JOIN顺序 - 扫描表的顺序
- 能够在加入之前对表应用过滤条件
使用ANSI-92 JOIN语法比ANSI-89有许多原因：
There are numerous reasons to use ANSI-92 JOIN syntax over ANSI-89:
- 更容易阅读，因为JOIN条件与WHERE子句分开
- 不太可能错过JOIN条件
- 对INNER以外的JOIN类型提供一致的语法支持，使查询易于在其他数据库上使用
- WHERE子句仅用于过滤加入的表的笛卡儿乘积
- More readable, as the JOIN criteria is separate from the WHERE clause
- Less likely to miss JOIN criteria
- Consistent syntax support for JOIN types other than INNER, making queries easy to use on other databases
- WHERE clause only serves as filtration of the cartesian product of the tables joined
ANSI -92 JOIN语法是模式，而不是反模式：
ANSI-92 JOIN syntax is pattern, not anti-pattern:
- 查询的目的更明显;应用程序使用的列是清楚的
- 它遵循关于在可能的情况下使用严格类型的模块化规则。显性几乎普遍更好。
缺乏熟悉和/或舒适，我没有看到继续使用ANSI-89 JOIN语法而不是ANSI-92语法的任何好处。一些人可能会抱怨说ANSI-92语法更冗长，但这是它的显式。越明确，越容易理解和维护。
Short of familiarity and/or comfort, I don't see any benefit to continuing to use ANSI-89 JOIN syntax instead of ANSI-92 syntax. Some might complain that ANSI-92 syntax is more verbose, but that's what makes it explicit. The more explicit, the easier it is to understand and maintain.
这篇关于在MySQL查询中，为什么要使用join而不是在哪里？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！