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.

Answer 1

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