MySQL 中逗号分隔的连接和语法上的连接有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20138355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the difference between comma separated joins and join on syntax in MySQL?
提问by Big Money
For example if I were to have a table "Person" with a column "id" that references a column "id" in table "Worker"
例如，如果我有一个表“Person”，其中一个“id”列引用了“Worker”表中的“id”列
What would the difference between these two queries be? They yield the same results.
这两个查询之间的区别是什么？它们产生相同的结果。
SELECT *
FROM Person
JOIN Worker
ON Person.id = Worker.id;
and
和
SELECT *
FROM Person,
Worker
WHERE Person.id = Worker.id;
Thanks
谢谢
采纳答案by sateesh
There is no difference at all.
完全没有区别。
Second representation makes query more readable and makes it look very clear as to which join corresponds to which condition.
第二种表示使查询更具可读性，并使哪个连接对应于哪个条件看起来非常清楚。
回答by spencer7593
The queries are logically equivalent. The comma operator is equivalent to an [INNER] JOINoperator.
查询在逻辑上是等效的。逗号运算符等效于[INNER] JOIN运算符。
The comma is the older style join operator. The JOIN keyword was added later, and is favored because it also allows for OUTER join operations.
逗号是旧式连接运算符。JOIN 关键字是后来添加的，受到青睐是因为它也允许 OUTER 连接操作。
It also allows for the join predicates (conditions) to be separated from the WHEREclause into an ONclause. That improves (human) readability.
它还允许将连接谓词（条件）从WHERE子句中分离成一个ON子句。这提高了（人类）可读性。
FOLLOWUP
跟进
This answer says that the two queries in the question are equivalent. We shouldn't mix old-school comma syntax for join operation with the newer JOINkeyword syntax in the same query. If we do mix them, we need to be aware of a difference in the order of precedence.
此答案表示问题中的两个查询是等效的。我们不应该JOIN在同一查询中将用于连接操作的旧式逗号语法与较新的关键字语法混合使用。如果我们混合使用它们，我们需要注意优先顺序的不同。
excerpt from MySQL Reference Manual
摘自 MySQL 参考手册
https://dev.mysql.com/doc/refman/5.6/en/join.html
https://dev.mysql.com/doc/refman/5.6/en/join.html
INNER JOINand,(comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).However, the precedence of the comma operator is less than that of
INNER JOIN,CROSS JOIN,LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the formUnknown column 'col_name' in 'on clause'may occur. Information about dealing with this problem is given later in this section.
INNER JOIN和,(逗号) 在没有连接条件的情况下在语义上是等效的：两者都在指定的表之间产生笛卡尔积（即，第一个表中的每一行都连接到第二个表中的每一行）。但是，逗号运算符的优先级低于
INNER JOIN,CROSS JOIN,LEFT JOIN, 等。如果在有连接条件的情况下将逗号连接与其他连接类型混合使用，Unknown column 'col_name' in 'on clause'可能会出现格式错误。本节稍后将提供有关处理此问题的信息。
回答by veljasije
Beside better readability, there is one more case where explicitly joined tables are better instead of comma-separated tables.
除了更好的可读性之外，还有一种情况是显式连接的表比逗号分隔的表更好。
let's see an example:
让我们看一个例子：
Create Table table1
(
ID int NOT NULL Identity(1, 1) PRIMARY KEY ,
Name varchar(50)
)
Create Table table2
(
ID int NOT NULL Identity(1, 1) PRIMARY KEY ,
ID_Table1 INT NOT NULL
)
Following query will give me all columns and rows from both tables
以下查询将为我提供两个表中的所有列和行
SELECT
*
FROM table1, table2
Following query will give me columns from first table with table alias called 'table2'
以下查询将为我提供第一个表中的列，表别名为“table2”
SELECT
*
FROM table1 table2
If you mistakenly forget comma in comma-separated join, second table automatically convert to table alias for first table. Not in all cases, but there is chances for something like this
如果您在逗号分隔连接中错误地忘记了逗号，则第二个表会自动转换为第一个表的表别名。并非在所有情况下，但有可能发生这样的事情
回答by vhadalgi
Using JOINS makes the code easier to read, since it's self-explanatory.
使用 JOINS 使代码更易于阅读，因为它是不言自明的。
In speed there is no difference (I tested it) and the execution plan is the same
在速度上没有区别（我测试过）并且执行计划是相同的
If the query optimizer is doing its job right, there should be no difference between those queries. They are just two ways to specify the same desired result.
如果查询优化器的工作正常，那么这些查询之间应该没有区别。它们只是指定相同所需结果的两种方法。
回答by Vinayak Pahalwan
The SELECT * FROM table1, table2, etc.is good for a couple of tables, but it becomes exponentially harder as the number of tables increases.
The SELECT * FROM table1, table2, etc.适用于几张桌子，但随着桌子数量的增加，它变得呈指数级增长。
The JOINsyntax makes it explicit what criteria affects which tables (giving a condition). Also, the second way is the older standard.
该JOIN语法使得它明确的标准是什么影响了哪些表（给人一种条件）。此外，第二种方式是较旧的标准。
Although, to the database, they end up being the same
虽然，对于数据库，它们最终是相同的
