Is it relevant to add verification on both 'on join' and 'where'?

Question

I would like to know if there is any difference between these queries:

1)

SELECT
 ...
FROM A
    JOIN B on B.AId = A.Id and B.X = @x
WHERE
    A.Id = 1
    and B.X = @x

2)

SELECT
 ...
FROM A
    JOIN B on B.AId = A.Id
WHERE
    A.Id = 1
    and B.X = @x

3)

SELECT
 ...
FROM A
    JOIN B on B.AId = A.Id and B.X = @x
WHERE
    A.Id = 1

Go to official documention and learn the definitions of expressions using cross join, inner join on & where. [This may help.](https://stackoverflow.com/a/25957600/3404097) — philipxy, May 23 '17 at 03:33
The best way to answer this sort of question is to generate [execution plans](https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx). In this case, they should (unless things are seriously odd) all generate exactly the same plan. In SQL, you're telling the system *what you want*, not *how to do it*, so provided two queries are *logically* asking for the same result, they *should* generate identical (or at least largely similar) execution plans. — Damien_The_Unbeliever, May 23 '17 at 06:07

score 1 · Answer 1 · answered May 23 '17 at 02:27

Because you are using an INNER JOIN there is no difference. Records are only kept where the join condition is true. If you filter results from b before the join (by specifying in the ON) or afterwards (by specifying in the WHERE) you'll end up with the same result set. (before and after is sort of arbitrary here, but it helps to think through it that way)

Also, your first query is not great since you filter on @x in two different spots. That is superfluous. My preference would be option 2.

score 0 · Answer 2 · answered May 23 '17 at 06:03

If you go back to the origins of SQL there was no specific "join" syntax, instead all filtering was part of the where clause

SELECT
 ...
FROM A, B
WHERE
    A.Id = 1
    and B.AId = A.Id
    and B.X = @x

So, whilst I absolutely don't wish to promote this older style syntax, it may help you understand that there really is no difference to your options 2 and 3 to the one I have just presented.

Your option 1 has a repeated predicate (and B.X = @x) which most optimizers will probably ignore, but even that option produces that same result.

score 0 · Answer 3 · answered May 23 '17 at 07:29

Filtering in where or Inner Join will do the same job. 'Where' filtering is preferred over filtering in the 'Join'(will be looking good). It may have impact when the join is not 'Inner Join', Say 'Left Outer Join' or 'Right Outer Join'

Which SQL query is faster? Filter on Join criteria or Where clause?

Is it relevant to add verification on both 'on join' and 'where'?

3 Answers3