MySQL 使用逗号 (,) 左连接或从多个表中选择

Left join or select from multiple table using comma (,)

提问by bbnn

I'm curious as to why we need to use LEFT JOINsince we can use commas to select multiple tables.

我很好奇为什么我们需要使用，LEFT JOIN因为我们可以使用逗号来选择多个表。

What are the differences between LEFT JOINand using commas to select multiple tables.

LEFT JOIN和使用逗号选择多个表有什么区别。

Which one is faster?

哪个更快？

Here is my code:

这是我的代码：

   SELECT mw.*, 
          nvs.* 
     FROM mst_words mw 
LEFT JOIN (SELECT no as nonvs, 
                  owner, 
                  owner_no, 
                  vocab_no, 
                  correct 
             FROM vocab_stats 
            WHERE owner = 1111) AS nvs ON mw.no = nvs.vocab_no 
    WHERE (nvs.correct > 0 ) 
      AND mw.level = 1

...and:

...和：

SELECT * 
  FROM vocab_stats vs, 
       mst_words mw 
 WHERE mw.no = vs.vocab_no 
   AND vs.correct > 0 
   AND mw.level = 1 
   AND vs.owner = 1111

采纳答案by RichardTheKiwi

First of all, to be completely equivalent, the first query should have been written

首先，要完全等价，第一个查询应该已经写好了

   SELECT mw.*, 
          nvs.* 
     FROM mst_words mw 
LEFT JOIN (SELECT *
             FROM vocab_stats 
            WHERE owner = 1111) AS nvs ON mw.no = nvs.vocab_no 
    WHERE (nvs.correct > 0 ) 
      AND mw.level = 1

So that mw.* and nvs.* together produce the same set as the 2nd query's singular *. The query as you have written can use an INNER JOIN, since it includes a filter on nvs.correct.

这样 mw.* 和 nvs.* 一起产生与第二个查询的单数 * 相同的集合。您编写的查询可以使用 INNER JOIN，因为它包含 nvs.correct 上的过滤器。

The general form

一般形式

TABLEA LEFT JOIN TABLEB ON <CONDITION>

attemptsto find TableB records based on the condition. If the fails, the results from TABLEA are kept, with all the columns from TableB set to NULL. In contrast

attempts根据条件查找TableB记录。如果失败，则保留 TABLEA 中的结果，并将 TableB 中的所有列设置为 NULL。相比之下

TABLEA INNER JOIN TABLEB ON <CONDITION>

also attemptsto find TableB records based on the condition. However, when fails, the particular record from TableA is removed from the output result set.

还attempts可以根据条件查找 TableB 记录。但是，当失败时，TableA 中的特定记录将从输出结果集中删除。

The ANSI standard for CROSS JOIN produces a Cartesian productbetween the two tables.

用于 CROSS JOIN 的 ANSI 标准在两个表之间生成笛卡尔积。

TABLEA CROSS JOIN TABLEB
  -- # or in older syntax, simply using commas
TABLEA, TABLEB

The intention of the syntax is that EACH row in TABLEA is joined to EACH row in TABLEB. So 4 rows in A and 3 rows in B produces 12 rows of output. When paired with conditions in the WHERE clause, it sometimes produces the same behaviour of the INNER JOIN, since they express the same thing (condition between A and B => keep or not). However, it is a lot clearer when reading as to the intention when you use INNER JOIN instead of commas.

该语法的目的是将 TABLEA 中的每一行连接到 TABLEB 中的每一行。所以 A 中的 4 行和 B 中的 3 行产生 12 行输出。当与 WHERE 子句中的条件配对时，它有时会产生与 INNER JOIN 相同的行为，因为它们表达相同的东西（A 和 B 之间的条件 => 保持与否）。但是，在阅读使用 INNER JOIN 而不是逗号时的意图时，它会更加清晰。

Performance-wise, most DBMS will process a LEFT join faster than an INNER JOIN. The comma notation can cause database systems to misinterpret the intention and produce a bad query plan - so another plus for SQL92 notation.

在性能方面，大多数 DBMS 处理 LEFT 连接的速度比处理 INNER JOIN 快。逗号表示法可能会导致数据库系统误解意图并产生错误的查询计划 - 所以 SQL92 表示法的另一个优点。

Why do we need LEFT JOIN?If the explanation of LEFT JOIN above is still not enough (keep records in A without matches in B), then consider that to achieve the same, you would need a complex UNION between two sets using the old comma-notation to achieve the same effect. But as previously stated, this doesn't apply to your example, which is really an INNER JOIN hiding behind a LEFT JOIN.

为什么需要LEFT JOIN？如果上面对 LEFT JOIN 的解释还不够（在 B 中保留记录而不在 B 中匹配），那么考虑到要实现相同的效果，您需要使用旧的逗号符号在两个集合之间进行复杂的 UNION 来实现相同的效果. 但是如前所述，这不适用于您的示例，这实际上是隐藏在 LEFT JOIN 后面的 INNER JOIN。

Notes:

笔记：

• The RIGHT JOIN is the same as LEFT, except that it starts with TABLEB (right side) instead of A.
• RIGHT and LEFT JOINS are both OUTER joins. The word OUTER is optional, i.e. it can be written as LEFT OUTER JOIN.
• The third type of OUTER join is FULL OUTER join, but that is not discussed here.

• RIGHT JOIN 与 LEFT 相同，只是它以 TABLEB（右侧）而不是 A 开头。
• RIGHT 和 LEFT JOINS 都是外连接。单词 OUTER 是可选的，即它可以写成LEFT OUTER JOIN.
• 第三种类型的 OUTER join 是 FULL OUTER join，但这里不讨论。

回答by Bazmatiq

Separating the JOIN from the WHERE makes it easy to read, as the join logic cannot be confused with the WHERE conditions. It will also generally be faster as the server will not need to conduct two separate queries and combine the results.

将 JOIN 与 WHERE 分开使其易于阅读，因为连接逻辑不能与 WHERE 条件混淆。它通常也会更快，因为服务器不需要进行两个单独的查询并组合结果。

The two examples you've given are not really equivalent, as you have included a sub-query in the first example. This is a better example:

您给出的两个示例实际上并不等效，因为您在第一个示例中包含了一个子查询。这是一个更好的例子：

SELECT vs.*, mw.*
FROM vocab_stats vs, mst_words mw
LEFT JOIN vocab_stats vs ON mw.no = vs.vocab_no
WHERE vs.correct > 0 
AND mw.level = 1
AND vs.owner = 1111