Q: WHERE clause followed by JOIN 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';

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

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?
EDIT: 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.

A: 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'

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.
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'


A: Slightly simplifed, you can't put a WHERE clause before the JOIN, it needs to go after it according to the syntax rules. The optimizer will figure the best order of execution out and won't necessarily execute things in the order you list them anyway.
SELECT big.a, big.b, small.b as small_b, small.c, small.d 
FROM big 
LEFT JOIN small
  ON big.b = small.b
WHERE foo = 'bar';

EDIT: If the query is slow with no or minimal hits, the likely cause is missing indexes on foo. 
Add an index on foo, and both big.b and small.b and you should see speedup.
