在 MySQL 查询中，为什么使用 join 而不是 where？

In MySQL queries, why use join instead of where?

提问by extraeee

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?

好像要组合两个或多个表，我们可以使用 join 或 where。一个比另一个有什么优势？

回答by OMG Ponies

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:

任何涉及多个表的查询都需要某种形式的关联来将表“A”的结果链接到表“B”。这样做的传统 (ANSI-89) 方法是：

1. List the tables involved in a comma separated list in the FROM clause

2. Write the association between the tables in the WHERE clause

   SELECT *
     FROM TABLE_A a,
          TABLE_B b
    WHERE a.id = b.id
   

1. 在 FROM 子句中列出逗号分隔列表中涉及的表

2. 在 WHERE 子句中写入表之间的关联

   SELECT *
     FROM TABLE_A a,
          TABLE_B b
    WHERE a.id = b.id
   

Here's the query re-written using ANSI-92 JOIN syntax:

这是使用 ANSI-92 JOIN 语法重写的查询：

SELECT *
  FROM TABLE_A a
  JOIN TABLE_B b ON b.id = a.id

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:

在支持（Oracle 9i+、PostgreSQL 7.2+、MySQL 3.23+、SQL Server 2000+）的情况下，使用任何一种语法都没有性能优势。优化器将它们视为相同的查询。但是更复杂的查询可以从使用 ANSI-92 语法中受益：

• Ability to control JOIN order - the order which tables are scanned
• Ability to apply filter criteria on a table prior to joining

• 能够控制 JOIN 顺序 - 扫描表的顺序
• 能够在加入之前对表应用过滤条件

From a Maintenance Perspective:

从维护的角度来看：

---

---

There are numerous reasons to use ANSI-92 JOIN syntax over ANSI-89:

在 ANSI-89 上使用 ANSI-92 JOIN 语法的原因有很多：

• 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

• 更具可读性，因为 JOIN 条件与 WHERE 子句分开
• 不太可能错过 JOIN 标准
• 对 INNER 以外的 JOIN 类型的一致语法支持，使查询易于在其他数据库上使用
• WHERE 子句仅用作连接表的笛卡尔积的过滤

From a Design Perspective:

从设计角度：

---

---

ANSI-92 JOIN syntax is pattern, not anti-pattern:

ANSI-92 JOIN 语法是模式，而不是反模式：

• The purpose of the query is more obvious; the columns used by the application is clear
• It follows the modularity rule about using strict typing whenever possible. Explicit is almost universally better.

• 查询的目的更明显；应用程序使用的列是明确的
• 它遵循尽可能使用严​​格类型的模块化规则。显式几乎普遍更好。

Conclusion

结论

---

---

Short of familiarity and/or comfort, I don't see any benefit to continuing to use the ANSI-89 WHERE clause instead of the ANSI-92 JOIN 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.

缺乏熟悉度和/或舒适度，我认为继续使用 ANSI-89 WHERE 子句而不是 ANSI-92 JOIN 语法没有任何好处。有些人可能会抱怨 ANSI-92 语法更冗长，但这正是它明确的原因。越明确，越容易理解和维护。

回答by James Cronen

Most people tend to find the JOIN syntax a bit clearer as to what is being joined to what. Additionally, it has the benefit of being a standard.

大多数人倾向于发现 JOIN 语法更清楚地了解什么被连接到什么。此外，它还具有成为标准的好处。

Personally, I "grew up" on WHEREs, but the more I use the JOIN syntax the more I'm starting to see how it's more clear.

就我个人而言，我在 WHEREs 上“长大”，但是我使用 JOIN 语法的次数越多，我就越开始明白它是如何变得更加清晰。

回答by HLGEM

These are the problems with using the where syntax (other wise known as the implicit join):

这些是使用 where 语法（也称为隐式连接）的问题：

First, it is all too easy to get accidental cross joins because the join conditions are not right next to the table names. If you have 6 tables being joined together, it is easy to miss one in the where clause. You will see this fixed all too often by using the distinct keyword. This is ahuge performance hit for the database. You can't get an accidental cross join using the explicit join syntax as it will fail the syntax check.

首先，意外交叉联接太容易了，因为联接条件并不紧邻表名。如果将 6 个表连接在一起，则很容易在 where 子句中遗漏一个。通过使用 distinct 关键字，您会经常看到此问题已得到解决。这对数据库来说是巨大的性能损失。您不能使用显式连接语法获得意外的交叉连接，因为它会使语法检查失败。

Right and left joins are problematic (In SQl server you are not guaranteed to get the correct results) in the old syntax in some databases. Further they are deprecated in SQL Server I know.

在某些数据库的旧语法中，左右联接是有问题的（在 SQl 服务器中，您不能保证获得正确的结果）。此外，我知道它们在 SQL Server 中已被弃用。

If you intend to use a cross join, that is not clear from the old syntax. It is clear using the current ANSII standard.

如果您打算使用交叉连接，旧语法中并不清楚。很明显使用当前的 ANSII 标准。

It is much harder for the maintainer to see exactly which fields are part of the join or even which tables join together in what order using the implicit syntax. This means it might take more time to revise the queries. I have known very few people who, once they took the time to feel comfortable with the explicit join syntax, ever went back to the old way.

对于维护者来说，使用隐式语法准确查看哪些字段是连接的一部分，甚至哪些表以什么顺序连接在一起要困难得多。这意味着修改查询可能需要更多时间。我认识的人很少，一旦他们花时间对显式连接语法感到满意，就会回到旧方式。

I've also noticed that some people who use these implicit joins don't actually understand how joins work and thus are getting incorrect results in their queries.

我还注意到一些使用这些隐式连接的人实际上并不了解连接的工作原理，因此在他们的查询中得到了不正确的结果。

Honestly, would you use any other kind of code that was replaced with a better method 18 years ago?

老实说，您会使用 18 年前被更好方法替换的任何其他类型的代码吗？

回答by Otávio Décio

Explicit joins convey intent, leaving the where clause to do the filtering. It is cleaner and it is standard, and you can do things such as left outer or right outer which is harder to do only with where.

显式连接传达意图，留下 where 子句进行过滤。它更干净，它是标准的，你可以做左外或右外之类的事情，这些事情很难做到。

回答by Mark Byers

You can't use WHERE to combine two tables. What you can do though is to write:

您不能使用 WHERE 来组合两个表。你可以做的是写：

SELECT * FROM A, B
WHERE ...

The comma here is equivalent to writing:

这里的逗号相当于写：

SELECT *
FROM A
CROSS JOIN B
WHERE ...

Would you write that? No - because it's not what you mean at all. You don't want a cross join, you want an INNER JOIN. But when you write comma, you're saying CROSS JOIN and that's confusing.

你会这样写吗？不 - 因为这根本不是你的意思。您不需要交叉联接，而是需要内部联接。但是当你写逗号时，你是在说 CROSS JOIN 这令人困惑。

回答by Gerhard Liebenberg

Actually you often need both "WHERE" and "JOIN".

实际上你经常需要“WHERE”和“JOIN”。

"JOIN" is used to retrieve data from two tables - based ON the values of a common column. If you then want to further filter this result, use the WHERE clause.

“JOIN”用于从两个表中检索数据 - 基于公共列的值。如果您想进一步过滤此结果，请使用 WHERE 子句。

For example, "LEFT JOIN" retrieves ALL rows from the left table, plus the matching rows from the right table. But that does not filter the records on any specific value or on other columns that are not part of the JOIN. Thus, if you want to further filter this result, specify the extra filters in the WHERE clause.

例如，“LEFT JOIN”检索左表中的所有行，以及右表中的匹配行。但这不会过滤任何特定值或不属于 JOIN 的其他列的记录。因此，如果要进一步过滤此结果，请在 WHERE 子句中指定额外的过滤器。