MySQL连接的计算顺序是什么?

I have the following query:

我有以下查询:

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;

I have the following questions:

我有以下问题:

Is the USING syntax synonymous with ON syntax?
使用语法是语法的同义词吗?
Are these joins evaluated left to right? In other words, does this query say: x = companies JOIN users; y = x JOIN jobs; z = y JOIN useraccounts;
这些连接是由左到右计算的吗?换句话说，这个查询是否说:x =公司加入用户;y = x加入工作;z = y加入用户帐户;
If the answer to question 2 is yes, is it safe to assume that the companies table has companyid, userid and jobid columns?
如果问题2的答案是肯定的，那么假设公司表有companyid、userid和jobid列是安全的吗?
I don't understand how the WHERE clause can be used to pick rows on the companies table when it is referring to the alias "j"
我不明白WHERE子句是如何在引用别名“j”时选择公司表上的行

Any help would be appreciated!

如有任何帮助，我们将不胜感激!

7 个解决方案

#1

USING (fieldname) is a shorthand way of saying ON table1.fieldname = table2.fieldname.
使用(fieldname)是表1中的一种简写方式。字段名= table2.fieldname。
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.
SQL没有定义完成连接的“顺序”，因为它不是语言的本质。显然，一个顺序必须在语句中指定，但是一个内部连接可以被认为是可交换的:您可以以任何顺序列出它们，您将得到相同的结果。

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.
也就是说，当构造一个SELECT…连接，特别是包含左连接的连接，我发现将第三个连接视为第一个连接的结果，将第四个连接视为第二个连接的结果，等等是有意义的。

More rarely, the specified order can influence the behaviour of the query optimizer, due to the way it influences the heuristics.
更少见的是，由于查询优化器对启发式的影响，指定的顺序可能会影响查询优化器的行为。
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.
不。查询的装配方式要求公司和用户都有一个companyid, jobs有一个userid, jobid, useraccounts有一个userid。但是，只有一个公司或用户需要一个userid才能让JOIN工作。
The WHERE clause is filtering the whole result -- i.e. all JOINed columns -- using a column provided by the jobs table.
WHERE子句使用jobs表提供的列过滤整个结果(即所有已连接列)。

7 个解决方案

#1

更多相关文章

随机推荐