内部连接vs WHERE子句

For simplicity, assume all relevant fields are NOT NULL.

为了简单起见，假设所有相关字段都不是空的。

You can do:

你能做什么:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

否则:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Do these two work on the same way in MySQL?

这两种方法在MySQL中是一样的吗?

10 个解决方案

#1

INNER JOIN is ANSI syntax which you should use.

内部连接是应该使用的ANSI语法。

It is generally considered more readable, especially when you join lots of tables.

它通常被认为更具可读性，尤其是当你加入很多表时。

It can also be easily replaced with an OUTER JOIN whenever a need arises.

当需要时，也可以用外部连接替换它。

The WHERE syntax is more relational model oriented.

WHERE语法是面向关系模型的。

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

两个连接表的结果是应用过滤器的表的笛卡尔积，过滤器只选择那些与连接列匹配的行。

It's easier to see this with the WHERE syntax.

用WHERE语法更容易看出这一点。

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

对于您的示例，在MySQL(通常在SQL中)中，这两个查询是同义词。

Also note that MySQL also has a STRAIGHT_JOIN clause.

还要注意，MySQL也有一个直接连接子句。

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

使用这个子句，您可以控制连接顺序:在外部循环中扫描哪个表，在内部循环中扫描哪个表。

You cannot control this in MySQL using WHERE syntax.

在MySQL中，不能使用WHERE语法来控制它。

#2

Others have pointed out that INNER JOIN helps human readability, and that's a top priority; I agree. Let me try to explain why the join syntax is more readable.

其他人指出，内部连接有助于人类的可读性，这是首要任务;我同意。让我试着解释为什么连接语法更容易阅读。

A basic SELECT query is this:

一个基本的选择查询是:

SELECT stuff
FROM tables
WHERE conditions

The SELECT clause tells us what we're getting back; the FROM clause tells us where we're getting it from, and the WHERE clause tells us which ones we're getting.

SELECT子句告诉我们得到什么;FROM子句告诉我们从哪里得到，where子句告诉我们从哪里得到。

JOIN is a statement about the tables, how they are bound together (conceptually, actually, into a single table). Any query elements that control the tables - where we're getting stuff from - semantically belong to the FROM clause (and of course, that's where JOIN elements go). Putting joining-elements into the WHERE clause conflates the which and the where-from; that's why the JOIN syntax is preferred.

JOIN是关于表的语句，它们是如何被绑定在一起的(实际上是概念上的，在一个表中)。任何控制表的查询元素——我们从哪里获得东西——在语义上属于from子句(当然，这就是连接元素的去向)。在WHERE子句中加入连接元素，将which和WHERE合并;这就是为什么首选连接语法。

#3

Applying conditional statements in ON / WHERE

在ON / WHERE中应用条件语句

Here I have explained about the logical query processing steps.

这里我已经解释了逻辑查询处理步骤。

---

Reference : Inside Microsoft® SQL Server™ 2005 T-SQL Querying
Publisher: Microsoft Press
Pub Date: March 07, 2006
Print ISBN-10: 0-7356-2313-9
Print ISBN-13: 978-0-7356-2313-2
Pages: 640

参考:Microsoft®SQL Server™2005 t - SQL查询出版者:微软出版社发布日期:07年3月,2006打印ISBN-10:0-7356-2313-9打印ISBN-13:978-0-7356-2313-2:640页

Inside Microsoft® SQL Server™ 2005 T-SQL Querying

在Microsoft®SQL Server™2005 t - SQL查询

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last.

与其他编程语言不同的SQL的第一个值得注意的方面是处理代码的顺序。在大多数编程语言中，代码按照编写的顺序进行处理。在SQL中，处理的第一个子句是FROM子句，而SELECT子句(首先出现)几乎是最后处理的。

Each step generates a virtual table that is used as the input to the following step. These virtual tables are not available to the caller (client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in a query, the corresponding step is simply skipped.

每个步骤都生成一个虚拟表，作为以下步骤的输入。这些虚拟表对调用者(客户端应用程序或外部查询)不可用。只有最后一步生成的表返回给调用者。如果查询中没有指定某个子句，则只需跳过相应的步骤。

Brief Description of Logical Query Processing Phases

Don't worry too much if the description of the steps doesn't seem to make much sense for now. These are provided as a reference. Sections that come after the scenario example will cover the steps in much more detail.

如果现在对步骤的描述似乎没有太大意义，不要太担心。这些是作为参考提供的。场景示例后面的部分将更详细地介绍步骤。

1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.

   FROM:在FROM子句中的前两个表之间执行一个笛卡尔积(cross join)，结果生成虚拟表VT1。

2. ON: The ON filter is applied to VT1. Only rows for which the <join_condition> is TRUE are inserted to VT2.

   ON: ON滤波器应用于VT1。只有 为真的行才会插入到VT2中。

3. OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows, generating VT3. If more than two tables appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed.

   外部(连接):如果指定了外部连接(与交叉连接或内部连接相对)，则未找到匹配的保留表或表中的行作为外部行添加到VT2中的行中，生成VT3。如果FROM子句中出现超过两个表，那么在FROM子句中最后一个连接的结果和下一个表之间重复应用步骤1到3，直到所有表都被处理。

4. WHERE: The WHERE filter is applied to VT3. Only rows for which the <where_condition> is TRUE are inserted to VT4.

   其中:将过滤器应用于VT3。只将 为真的行插入到VT4。

5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.

   GROUP BY:根据GROUP BY子句中指定的列列表，VT4中的行被分组。VT5生成。

6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.

   CUBE | ROLLUP:将超组(组)添加到VT5的行中，生成VT6。

7. HAVING: The HAVING filter is applied to VT6. Only groups for which the <having_condition> is TRUE are inserted to VT7.

   具有:具有滤波器应用于VT6。只有 为真的组才插入到VT7。

8. SELECT: The SELECT list is processed, generating VT8.

   SELECT列表被处理，生成VT8。

9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.

   区别:从VT8中删除重复的行。VT9生成。

10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).

    ORDER BY: VT9中的行按照ORDER BY子句中指定的列列表进行排序。生成游标(VC10)。

11. TOP: The specified number or percentage of rows is selected from the beginning of VC10. Table VT11 is generated and returned to the caller.

    顶部:从VC10开始选择指定的行数或百分比。生成表VT11并返回给调用者。

---

Therefore, (INNER JOIN) ON will filter the data (the data count of VT will be reduced here itself) before applying WHERE clause. The subsequent join conditions will be executed with filtered data which improves performance. After that only the WHERE condition will apply filter conditions.

(Applying conditional statements in ON / WHERE will not make much difference in few cases. This depends how many tables you have joined and number of rows available in each join tables)

在ON / WHERE中应用条件语句在少数情况下不会有太大的区别。这取决于您连接了多少个表以及每个连接表中可用的行数)

#4

The implicit join ANSI syntax is older, less obvious and not recommended.

隐式连接ANSI语法更老，不太明显，也不推荐。

In addition, the relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer.

此外，关系代数允许WHERE子句和内部连接中的谓词的可互换性，因此，即使是包含WHERE子句的内部连接查询也可以由优化器重新排列谓词。

I recommend you write the queries in the most readble way possible.

我建议您以最易读的方式编写查询。

Sometimes this includes making the INNER JOIN relatively "incomplete" and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.

有时，这包括使内部连接相对“不完整”，并将一些标准放在简单的地方，使过滤标准的列表更容易维护。

For example, instead of:

例如,而不是:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Write:

写:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

But it depends, of course.

当然，这要看情况。

#5

Implicit joins (which is what your first query is known as) become much much more confusing, hard to read, and hard to maintain once you need to start adding more tables to your query. Imagine doing that same query and type of join on four or five different tables ... it's a nightmare.

隐式连接(您的第一个查询被称为隐式连接)在您需要向查询添加更多表时变得更加混乱、难于阅读和难于维护。假设在4或5个不同的表上执行相同的查询和连接类型……这是一个噩梦。

Using an explicit join (your second example) is much more readable and easy to maintain.

使用显式连接(您的第二个示例)更易于阅读和维护。

#6

I'll also point out that using the older syntax is more subject to error. If you use inner joins without an ON clause, you will get a syntax error. If you use the older syntax and forget one of the join conditions in the where clause, you will get a cross join. The developers often fix this by adding the distinct keyword (rather than fixing the join because they still don't realize the join itself is broken) which may appear to cure the problem, but will slow down the query considerably.

我还将指出，使用旧语法更容易出错。如果使用没有ON子句的内部连接，就会出现语法错误。如果您使用旧的语法并忘记where子句中的一个连接条件，您将得到一个交叉连接。开发人员通常通过添加不同的关键字(而不是修改连接，因为他们仍然没有意识到连接本身已经被破坏)来解决这个问题，这看起来可以解决问题，但是会大大降低查询速度。

Additionally for maintenance if you have a cross join in the old syntax, how will the maintainer know if you meant to have one (there are situations where cross joins are needed) or if it was an accident that should be fixed?

此外，对于维护，如果您在旧语法中有一个交叉连接，那么维护人员如何知道您是想要一个交叉连接(有些情况下需要交叉连接)，还是应该修复的意外?

Let me point you to this question to see why the implicit syntax is bad if you use left joins. Sybase *= to Ansi Standard with 2 different outer tables for same inner table

让我指出这个问题，看看如果使用左连接，为什么隐式语法不好。Sybase *= Ansi标准，同一个内表有两个不同的外表

Plus (personal rant here), the standard using the explicit joins is over 20 years old, which means implicit join syntax has been outdated for those 20 years. Would you write application code using syntax that has been outdated for 20 years? Why do you want to write database code that is?

另外，使用显式连接的标准已经有20多年的历史了，这意味着隐式连接语法已经过时20年了。您会使用过时了20年的语法编写应用程序代码吗?为什么要编写这样的数据库代码呢?

#7

They have a different human-readable meaning.

它们具有不同的人类可读的含义。

However, depending on the query optimizer, they may have the same meaning to the machine.

但是，根据查询优化器，它们可能对机器具有相同的含义。

You should always code to be readable.

您应该始终编写可读的代码。

That is to say, if this is a built-in relationship, use the explicit join. if you are matching on weakly related data, use the where clause.

也就是说，如果这是一个内置的关系，使用显式连接。如果您匹配弱相关数据，请使用where子句。

#8

The SQL:2003 standard changed some precedence rules so a JOIN statement takes precedence over a "comma" join. This can actually change the results of your query depending on how it is setup. This cause some problems for some people when MySQL 5.0.12 switched to adhering to the standard.

SQL:2003标准改变了一些优先规则，因此连接语句优先于“逗号”连接。这实际上可以根据查询的设置更改查询结果。当MySQL 5.0.12切换到标准时，这给一些人带来了一些问题。

So in your example, your queries would work the same. But if you added a third table: SELECT ... FROM table1, table2 JOIN table3 ON ... WHERE ...

在你的例子中，你的查询也是一样的。但是如果您添加了第三个表:SELECT…从表1，表2连接表3到…在那里……

Prior to MySQL 5.0.12, table1 and table2 would be joined first, then table3. Now (5.0.12 and on), table2 and table3 are joined first, then table1. It doesn't always change the results, but it can and you may not even realize it.

在MySQL 5.0.12之前，表1和表2先连接，然后是表3。现在(5.0.12和on)， table2和table3首先连接，然后是table1。它并不总是改变结果，但它可以，你甚至可能没有意识到。

I never use the "comma" syntax anymore, opting for your second example. It's a lot more readable anyway, the JOIN conditions are with the JOINs, not separated into a separate query section.

我不再使用“逗号”语法，选择第二个示例。无论如何，它的可读性要高得多，连接条件与连接一起使用，而不是被分割成单独的查询部分。

#9

I know you're talking about MySQL, but anyway: In Oracle 9 explicit joins and implicit joins would generate different execution plans. AFAIK that has been solved in Oracle 10+: there's no such difference anymore.

我知道你说的是MySQL，但是无论如何:在Oracle 9中显式连接和隐式连接将生成不同的执行计划。在Oracle 10+中已经解决的AFAIK:不再有这样的区别了。

#10

ANSI join syntax is definitely more portable.

ANSI连接语法无疑更具有可移植性。

I'm going through an upgrade of Microsoft SQL Server, and I would also mention that the =* and *= syntax for outer joins in SQL Server is not supported (without compatability mode) for 2005 sql server and later.

我正在对Microsoft SQL Server进行升级，我还要提到，对于2005年的SQL Server和以后的SQL Server，不支持SQL Server中外部连接的=*和*=语法(没有可压缩模式)。