在哪些情况下可以使用临时表来重写INNER JOIN（SELECT ...）(In which cases can INNER JOIN (SELECT … not be rewritten usin

在哪些情况下可以使用临时表来重写INNER JOIN（SELECT ...）(In which cases can INNER JOIN (SELECT … not be rewritten using temp table)

我正在使用GridSQL，在SQL模式INNER JOIN（SELECT出现）时我会遇到一些性能问题，因此我正在考虑将所有这些查询重写为两个查询，一个使用精确的select语句创建临时表，另一个使用临时连接表，所以这个模式应该是INNER JOIN temp_table（...）。

在哪种情况下这不起作用？ 正如你可能怀疑的那样，我希望这个问题没有答案。 ;）

I am using GridSQL where I get some performance problems whenever the SQL pattern INNER JOIN (SELECT arises. I am therefore considering rewriting all these queries into two queries, one creating a temporary table using the exact select statement and the other query joining with the temporary table, so the pattern would be INNER JOIN temp_table (...) instead.

In which cases would this not work? As you might suspect, I am hoping for no answers to this question. ;)

最满意答案

子选择唯一不能轻易分解为临时表的时间是它引用外部表中的字段时。

对于“... INNER JOIN（SELECT ...）”我怀疑你会遇到这个问题（或者甚至有可能，我不记得曾经尝试过），因为你应该只是指第一张表中的字段加入标准。

The only time a sub-select can't easily be broken off into a temp-table is when it references fields in the outer table.

For "... INNER JOIN (SELECT ..." I doubt you would have that issue (or if it's even possible, I don't recall ever trying it), since you should only be referring to fields from the first table in the join criteria.