联接语句的操作顺序

问题描述

给出以下三种方式加入

select t1.* from t1
left join t2 on t1.fk = t2.pk
join t3 on t2.fk = t3.pk

如果t2和t3之间的连接失败，是否将返回t1和t2之间成功连接的行?如果操作顺序是从左到右，那么我认为不是，但是如果从右到左求值(首先将t3与t2结合在一起)，那么即使前者失败，t1仍将返回.

它如何工作?

解决方案

ON子句的位置控制评估的逻辑顺序.

因此首先发生t1 LEFT JOIN t2 ON t1.fk = t2.pk.联接的结果是一个虚拟表，其中包含t1, t2中所有匹配的行，并且(因为它是左外部联接)，所有不匹配的t1行也都保留为t2列的空值. >

此虚拟表然后参与下一个联接. JOIN t3 ON t2.fk = t3.pk

任何与t1中的行不匹配的t2记录都不是第一阶段输出的虚拟表的一部分，因此不会出现在最终结果中.此外，t2.fk = t3.pk上的此内部联接将丢失任何t2.fk的NULL值，从而有效地将您的整个内容重新转换为内部联接.

对逻辑查询处理的解释很好作者Itzik Ben Gan在这里

Given the following 3 way join

select t1.* from t1
left join t2 on t1.fk = t2.pk
join t3 on t2.fk = t3.pk

If the join between t2 and t3 failed, would the row from the successful join between t1 and t2 be returned? If the order of operation goes from left to right, I assume not, but if it's evaluated from right to left (t3 is joined to t2 first) then t1 will still be returned even when the former failed.

How does it work?

解决方案

The placement of the ON clauses controls the logical order of evaluation.

So first the t1 LEFT JOIN t2 ON t1.fk = t2.pk happens. The result of this join is a virtual table containing all the matching rows from t1, t2 and (because it is a left outer join) any non matched t1 rows are also preserved with null values for the t2 columns.

This virtual table then participates in the next join. JOIN t3 ON t2.fk = t3.pk

Any t2 records that do not match rows in t1 are not part of the virtual table output from the first stage so won't appear in the final result. Additionally this inner join on t2.fk = t3.pk will lose any NULL values of t2.fk effectively turning your whole thing back into inner joins.

Logical Query Processing is explained well by Itzik Ben Gan here

这篇关于联接语句的操作顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！