如何在（选择）语句中最好地组织内部联接

let's say i have three tables, each one relates to another,

假设我有三个表,每个表与另一个表相关,

when i need to get a column from each table, does it make difference how to organize the (inner joins)??

当我需要从每个表中获取一列时,它是如何组织(内部联接)?

Select table1.column1,table2.column2,table3.column2
From table1 
Inner Join table2 on ..... etc
Inner Join table3 on .....

in another words, can i put (table2) after (From )??

换句话说,我可以把(table2)放在(From)之后吗?

Select table1.column1,table2.column2,table3.column2
From table2
Inner Join table1 on ..... etc
Inner Join table3 on .....

2 个解决方案

#1

For most queries, order does not matter.

对于大多数查询,订单无关紧要。

An INNER JOIN is both associative and commutative so table order does not matter

INNER JOIN既是关联的又是可交换的,因此表顺序无关紧要

SQL is declarative. That is, how you define the query is not how the optimiser works it out. It does not do it line by line as you wrote it.

SQL是声明性的。也就是说,您定义查询的方式不是优化器如何解决问题。当你写它时,它并不是一行一行的。

That said...

OUTER JOINs are neither associative nor commutative

OUTER JOIN既不是关联的也不是可交换的

For complex queries, the optimiser will "best guess" rather than go through all possibilities which "costs" too much. Table order may matter here

对于复杂查询,优化器将“最佳猜测”而不是经历“成本”过多的所有可能性。表顺序可能在这里很重要

#2

The inner join operation has left to right associativity. It doesn't matter much in which order you write the tables, as long as you don't refer to any tables in the ON condition before they have been joined.

内连接操作具有从左到右的关联性。只要在加入之前没有引用ON条件中的任何表,那么编写表的顺序并不重要。

When the statement is executed the database engine will determine the best order to execute the join and this may be different from the order they appear in the SQL query.

执行语句时,数据库引擎将确定执行连接的最佳顺序,这可能与它们在SQL查询中出现的顺序不同。

#1