8

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.

RoyalSwish
  • 1,503
  • 10
  • 31
  • 57
  • 1
    if you believe the error is caused by the syntax, please look at the data returned from both queries and create a simplified subset of that data that you can run these dummy queries against. if both queries return the correct results, the error is outside of this syntax. otherwise, post the sample data so we can recreate and diagnose. – Tanner Mar 12 '15 at 14:01
  • 1
    WHERE/ON differ for OUTER JOINs. But you have only INNER one. So it doesn't matter which clauses are under ON and which are under WHERE. Your problem must be due to another condition. – Matt Mar 12 '15 at 14:06
  • 3
    @RoyalSwish Buddy; Nothing wrong from your side. No need to say sorry. `where clause and on clause are different for outer joins but linguistically behave as same for inner join.` and your post is different from that post. I just meant your post is just not a duplicate of 'that' post – Tushar Mar 12 '15 at 14:18
  • @RoyalSwish But Buddy; If you would have used `outer joins` in you original queries and as you say: `the post was to cover all types of joins, I just picked INNER JOIN as an example, that's why the title just says 'join'` Then it surely matters. The above queries may bring different results for outer joins – Tushar Mar 12 '15 at 14:28
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72847/discussion-between-avidan-and-royalswish). – Tushar Mar 12 '15 at 14:42
  • 1
    possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Tab Alleman Mar 12 '15 at 14:43
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Jun 08 '22 at 11:57

1 Answers1

1

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

Matt
  • 13,674
  • 1
  • 18
  • 27