SQLTeam.com | Weblogs | Forums

Do WHERE statements filter the pre-join tables or the after-join table?

#1

Hello

I do:
SELECT *
FROM t1
LEFT JOIN t2 on col1=col2
WHERE t2.col3=1

Is the WHERE filter above applied to the t2 before the join or to the col3 column from t2 after the join?
Because it seems not to filter the after-join table.

How can I do each, i.e. filter the pre-join or the after-join table?

Thanks!

Is there an easy way to populate a column with an INDEX/MATCH statement?
#2

What research have you done so far. I think it Has already been answered for you.

#3

SQL itself decides when/how to apply the WHERE conditions. There is no one fixed answer, because while SQL aims for best efficiency, it must first be certain that it returns a correct result.

#4

That is true for an INNER JOIN - but not necessarily for an OUTER JOIN. In this case - the problem is that the OP is expecting rows from 't1' that do not exist in 't2' to be included in the results and is not seeing those results.

This isn't really about when the filter is applied - rather an issue of how outer joins work and NULL values.

Because NULL will be returned for col3 where a row exists in T1 but does not exist in T2 - that row will be excluded from the results because NULL can never be equal to 1.

#5

Thanks but how can I specify WHERE criteria in the join tables before the join and how after the join?

#6

I answered that above -- you can't.

2 Likes
#7

You cannot do that in the WHERE - it must be done in the ON portion of the join statement. For example:

FROM ...
JOIN ... ON t2.key = t1.key AND t2.col3 = 1

Or - you can do this:

FROM t1
JOIN t2 ON t2.col1 = t1.col1
WHERE (t2.col3 = 1 OR t2.col1 IS NULL)
#8

Even that will (most likely) not filter before the JOIN. The best try to do that is something like:

SELECT ... FROM ...
INNER JOIN ( SELECT ... FROM t2 WHERE col3 = 1 ) AS t2 ON t2.key = t1.key

but even that is not absolutely to be applied before the JOIN, SQL might do it as part of the join. Again, as long as SQL can guarantee that you still get a correct result, it can adjust the preliminary processing as it sees fit.

#9

If you are using INNER JOIN - that is absolutely correct. However - using an OUTER JOIN it is not correct. An OUTER JOIN will filter the ON clause before joining to the other tables.

The OP's question was related to LEFT JOIN and not an INNER JOIN.

#10

SQL could still do it as part of the join rather than "before" the JOIN. In theory SQL could even filter after the join if for some other reason in the query it decided it needed to. You can't safely assume the physical order of operations only the logical ones. That is, SQL will guarantee that the results will be as if it was applied before, no matter what order it physically did the comparisons.