WHERE子句后跟JOIN [英] WHERE clause followed by JOIN

问题描述

我有一个非常大的值表.我们将该表称为big.我有一个较小的表，称为small.我想加入这些表，但只能在big内的特定值上使用.

I have a very large table of values. Let's call that table big. I have a somewhat smaller table called small. I would like to join these tables, but only on specific values within big.

我可以通过说来获得价值

I could get the values by saying

SELECT a,b FROM big WHERE foo='bar';

我想将结果与small结合起来，说:

I would like to join that result with small, saying something like:

SELECT a,b FROM big WHERE foo='bar' 
LEFT JOIN (SELECT b as small_b,c,d FROM small) ON big.b=small.small_b

我不想将所有内容都拉到big中，因为这很麻烦，而且要花半分钟的时间.我只需要一小部分.我将如何去做?

I don't want to pull everything in big, because that's a lot of stuff and takes half a minute. I just need that small subset. How would I go about doing this?

我已经编辑查询以匹配最后的WHERE子句.即使我将其中一个表称为big，它实际上也有500,000行以下.我正在使用的测试用例查询没有使用在big中找到的任何值(big没有值，其中foo ='bar')，并且查询仍然花费了七秒钟的时间.这真的很正常吗?对我来说似乎太慢了.

I've edited my query to match the WHERE clause going at the end. Even though I call one of the tables big, it actually has under 500,000 rows. The test case query I was using did not use any values found in big (big has no value where foo='bar'), and the query still took over seven seconds. Is this really normal? It seems extremely slow to me.

推荐答案

我认为您将SQL语法误认为是RDBMS引擎执行的执行流:此查询

I think you mistake the syntax of SQL for the flow of execution performed by the RDBMS engine: this query

SELECT a,b FROM big b
LEFT JOIN (SELECT b as small_b,c,d FROM small) ON big.b=small.small_b
WHERE b.foo='bar'

将被优化为仅对通过foo='bar'条件过滤的big行执行联接，而不是对整个big执行任何值得其盐分的查询优化程序执行联接，尽管事实上WHERE子句以文字形式出现在JOIN之后.

will be optimized to perform a join only to the rows of big filtered by the foo='bar' condition, not on the entire big, by any query optimizer that is worth its salt, despite the fact that the WHERE clause appears textually after the JOIN.

您可能希望不用这样的内部SELECT来重写它:

You may want to rewrite this without an inner SELECT like this:

SELECT t1.a as big_a, t1.b as big_b, t2.b as small_b, t2.c, t2.d
FROM big t1
LEFT JOIN small t2 ON t1.b = t2.b
WHERE t1.foo='bar'

这篇关于WHERE子句后跟JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！