MySQL JOIN 的评估顺序是什么?

我有以下查询:

SELECT c.*
FROM companies AS c
JOIN users AS u USING(companyid)
JOIN jobs AS j USING(userid)
JOIN useraccounts AS us USING(userid)
WHERE j.jobid = 123;

我有以下问题:

1. USING 语法是否与 ON 语法同义?
2. 这些连接是从左到右计算的吗?换句话说，这个查询是否说:x = 公司加入用户；y = x JOIN 作业；z = y 加入用户帐户；
3. 如果问题 2 的答案是肯定的，那么假设公司表具有 companyid、userid 和 jobid 列是否安全?
4. 我不明白 WHERE 子句在引用别名j"时如何用于选择公司表上的行

任何帮助将不胜感激！

推荐答案

1. USING (fieldname) 是 ON table1.fieldname = table2.fieldname 的简写方式.

1. USING (fieldname) is a shorthand way of saying ON table1.fieldname = table2.fieldname.

SQL 没有定义 JOINS 的顺序"，因为它不是语言的本质.显然必须在语句中指定一个顺序，但 INNER JOIN 可以被认为是可交换的:您可以按任何顺序列出它们，您将得到相同的结果.

SQL doesn't define the 'order' in which JOINS are done because it is not the nature of the language. Obviously an order has to be specified in the statement, but an INNER JOIN can be considered commutative: you can list them in any order and you will get the same results.

也就是说，在构建 SELECT ... JOIN 时，尤其是包含 LEFT JOIN 的连接时，我发现将第三个 JOIN 视为将新表连接到第一个 JOIN、第四个 JOIN 的结果是有意义的作为加入第二个JOIN的结果，依此类推.

That said, when constructing a SELECT ... JOIN, particularly one that includes LEFT JOINs, I've found it makes sense to regard the third JOIN as joining the new table to the results of the first JOIN, the fourth JOIN as joining the results of the second JOIN, and so on.

更罕见的是，指定的顺序会影响查询优化器的行为，因为它影响启发式的方式.

More rarely, the specified order can influence the behaviour of the query optimizer, due to the way it influences the heuristics.

没有.查询的组装方式要求公司和用户都有一个 companyid，jobs 有一个 userid 和一个 jobid，而 useraccounts 有一个 userid.但是，只有一家公司或用户需要用户 ID 才能使 JOIN 工作.

No. The way the query is assembled, it requires that companies and users both have a companyid, jobs has a userid and a jobid and useraccounts has a userid. However, only one of companies or user needs a userid for the JOIN to work.

WHERE 子句使用作业表提供的列过滤整个结果——即所有 JOINed 列.

The WHERE clause is filtering the whole result -- i.e. all JOINed columns -- using a column provided by the jobs table.