SQL - JOIN BETWEEN vs WHERE BETWEEN

Question

A few weeks ago I did a query (can't remember what for) and it involved the BETWEEN operator. At first, I used this in the WHERE clause, but it didn't produce the correct result set (again, I don't remember what the problem was). I thought about why and eventually concluded that I had to move it to the JOIN clause.

e.g.

"Original" query:

SELECT --something
FROM table1 a
/*type of*/ JOIN table2 b ON a.field = b.field
WHERE b.field2 BETWEEN a.field2 AND a.field3 /*some other conditions*/

Revised query:

SELECT --something
FROM table1 a
/*type of*/ JOIN table2 b ON a.field = b.field 
                       AND b.field2 BETWEEN a.field2 AND a.field3
WHERE /*some other conditions*/

The JOIN version returned a smaller result set compared to the WHERE version, and the JOIN version produced the correct result set whereas WHERE one didn't.

The reason I'm asking now is because at the time I thought nothing of it, but today my boss questioned why I did it (for an entirely different query) and I told him the experience I had the last time I did, and both he and I are quite intrigued as to the differences between the two usages.

From what I think as to why one produced something different to the other is that the WHERE clause version only selected a range of data, whereas the JOIN version looked for a range of data to join on. Is this the case? Or I did come across a unique experience affected by the rest of the query (the query was much more complicated than the examples).

EDIT I disagree with the possible duplicate because my one looks specifically at the BETWEEN clause, not joins in general. I have read the mentioned post before and it didn't answer my question, hence why I posted this.

Answer 1

There's no difference between two versions, except if you have wrong precedence in one of them due to lack of parentheses.