Adding a filter in WHERE versus FROM

Question

So I know it is a good programming practice to add the filter conditions in the WHERE clause of a query so as to minimize the number of rows that are returned in the joins but when how do we decide if we should add a filter in the WHERE instead of the FROM? For example:

SELECT a.ColA, a.ColB, a.ColC, b.ColD, b.ColE
FROM TblA a INNER JOIN TblB b
On a.ColA = b.ColD
AND a.ColA = 'X' 
AND a.ColB = 'Y'
WHERE b.ColD = 'ABC'

In this query what if I added the b.ColD in the AND clause instead of the WHERE? Will that not make the query more efficient? I know the results could be different in both approaches sometimes but I don't know why?

Thanks.

Judging by the tsql tag, you're using SQL Server. The management studio has the ability to show the execution plan of a query, so you could compare the differences of both versions. My _guess_ is that there won't be much difference, though. — Mike P, May 15 '13 at 14:15
THis is not a duplicate: OP is asking about filters, not JOINs — gbn, May 15 '13 at 14:19
@gbn I'm not sure if the OP's wording ("filter" vs. "condition" etc.) matters so heavily, since the example given by the OP is essentially the same as [here](http://stackoverflow.com/q/1907335/880904), [here](http://stackoverflow.com/q/1018952/880904), [here](http://stackoverflow.com/q/1401889/880904), [here](http://stackoverflow.com/q/15483808/880904) and more. All of those have `JOIN...ON`, ask about `WHERE`, and include similar answers to yours. It would seem to me that this question has been answered, or should we also reopen [this question](http://stackoverflow.com/q/13145275/880904)? — Tim Lehner, May 15 '13 at 14:43
@TimLehner: That one if similar to the proposed dupes. In this case, I (and the dba.sw huys) read it as "should I filter in the JOIN" not "should I join in the WHERE". Maybe quite subtle, but how I read this question. OP also asks if it more efficient to "filter in the JOIN". He;s basically asking about predicate pushing within a single query http://stackoverflow.com/a/6654525/27535 — gbn, May 15 '13 at 15:56
@gbn With the possible exception of the third one, my examples are all "should I filter in the JOIN" questions. Anyway, I suppose there's no need to belabor the point. Your answer is excellent nonetheless. — Tim Lehner, May 15 '13 at 16:27

score 9 · Answer 1 · edited May 23 '17 at 10:32

It rarely makes a difference in my experience. It can, but not often. The query optimiser works this things out and you don't need to second guess it.

When using LEFT JOINs though, then it can matter because it changes the query semantics

Generally, I would separate JOIN and WHERE conditions for clarity and avoid ambiguities or partial/full cross joins. This answer is for more info only

Note: the question is not "JOIN in the WHERE difference" but "WHERE in the JOIN difference"

I would simply do this

SELECT
    a.ColA, a.ColB, a.ColC, b.ColD, b.ColE
FROM
    TblA a
    INNER JOIN
    TblB b On a.ColA = b.ColD
WHERE
     a.ColA = 'X' AND a.ColB = 'Y' AND b.ColD = 'ABC'

Or, if more complex and I wanted to make it read better
This can also help if the optimiser is being stupid (rare):

SELECT
    a.ColA, a.ColB, a.ColC, b.ColD, b.ColE
FROM
    (SELECT ColA, ColB, ColC FROM TblA
     WHERE ColA = 'X' AND ColB = 'Y') a
    INNER JOIN
    (SELECT ColD, ColE FROM TblB WHERE ColD = 'ABC') b On a.ColA = b.ColD

In this last case, one can use CTEs too to break it down further for readability

score 2 · Answer 2 · answered May 15 '13 at 14:18

In a declarative language such as SQL I find it helpful to be as declarative as possible i.e. keep JOIN logic ("how do I want to connect my data?") separate from WHERE logic ("what do I want to filter out?").

Technically it may make absolutely no difference, but it's a good sanity check if you force yourself to separate the two.

Adding a filter in WHERE versus FROM

2 Answers2