左联接或使用逗号(，)从多个表中选择 [英] Left join or select from multiple table using comma (,)
问题描述
我很好奇为什么需要使用LEFT JOIN，因为我们可以使用逗号来选择多个表.
I'm curious as to why we need to use LEFT JOIN since we can use commas to select multiple tables.
LEFT JOIN和使用逗号选择多个表之间有什么区别.
What are the differences between LEFT JOIN and using commas to select multiple tables.
哪个更快?
这是我的代码:
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
...并且:
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
推荐答案
首先，要完全等同，应该已经编写了第一个查询
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
因此mw.*和nvs.*一起产生与第二个查询的单数*相同的集合.您编写的查询可以使用INNER JOIN，因为它包含对nvs.correct的过滤器.
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.
一般形式
TABLEA LEFT JOIN TABLEB ON <CONDITION>
attempts 以根据条件查找TableB记录.如果失败，将保留来自TABLEA的结果，并将来自TableB的所有列都设置为NULL.相反
attempts to 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
TABLEA INNER JOIN TABLEB ON <CONDITION>
也 attempts 可以根据条件查找TableB记录. 但是，当失败时，TableA中的特定记录将从输出结果集中删除.
also attempts to find TableB records based on the condition. However, when fails, the particular record from TableA is removed from the output result set.
The ANSI standard for CROSS JOIN produces a Cartesian product between the two tables.
TABLEA CROSS JOIN TABLEB
-- # or in older syntax, simply using commas
TABLEA, TABLEB
该语法的意图是将TABLEA中的EACH行与TABLEB中的EACH行连接在一起.因此，A中的4行和B中的3行会产生12行输出.与WHERE子句中的条件配对时，有时会产生INNER JOIN相同的行为，因为它们表达的是相同的东西(A和B之间的条件=>保持或不保持).但是，在阅读有关使用INNER JOIN而不是逗号的意图时，要清楚得多.
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.
在性能方面，大多数DBMS处理LEFT连接的速度要比INNER JOIN更快.逗号表示法可能导致数据库系统误解其意图并产生错误的查询计划-SQL92表示法的另一个优点.
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.
我们为什么需要LEFT JOIN?如果上面对LEFT JOIN的解释仍然不够(保持A中的记录而B中没有匹配项)，则认为要达到相同的目的，您需要使用旧的逗号符号在两个集合之间使用复杂的UNION来达到相同的效果. 但是如前所述，这不适用于您的示例，它实际上是一个隐藏在LEFT JOIN后面的INNER JOIN.
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.
注意:
- RIGHT JOIN与LEFT相同，除了它以TABLEB(右侧)开头而不是A.
- RIGHT和LEFT JOINS都是OUTER联接.单词OUTER是可选的，即可以写为
LEFT OUTER JOIN. - OUTER联接的第三种类型是FULL OUTER联接，但这不在这里讨论.
这篇关于左联接或使用逗号(，)从多个表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
