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,353
  • 10
  • 25
  • 55
  • 1
    @CoderofCode I disagree; look at edit. – RoyalSwish Mar 12 '15 at 13:56
  • Show full query(s). I guess you have problem with parentheses. – Matt Mar 12 '15 at 13:58
  • 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
  • @user4419802 I can't as the query was just a one off one which was trashed after use. Like I said, I don't remember what it was for. – RoyalSwish Mar 12 '15 at 14:01
  • @CoderofCode that one is closer to my question, but it will be interesting to see if someone can clarify even further about my question, but it's similar to what I thought. – RoyalSwish Mar 12 '15 at 14:03
  • 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
  • @CoderofCode Buddy; That example is for `Left Join`. He is asking for `inner join`. – Avidan Mar 12 '15 at 14:11
  • @user4419802 I read the post CoderofCode mentioned and it said what you said, but he said that both are semantically differently, so the question is why does an outer join produce a different result set. – RoyalSwish Mar 12 '15 at 14:12
  • @Avidan Sorry, 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'. – RoyalSwish Mar 12 '15 at 14:13
  • 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 – Avidan 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 – Avidan Mar 12 '15 at 14:28
  • @Avidan Yep, I've amended it to imply any type of join. – RoyalSwish Mar 12 '15 at 14:29
  • Had you used inner join itself? or Outer join? – Avidan Mar 12 '15 at 14:30
  • @Avidan I vaguely remember it being a left outer – RoyalSwish Mar 12 '15 at 14:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72847/discussion-between-avidan-and-royalswish). – Avidan Mar 12 '15 at 14:42
  • Then you shall surely go through the links given by CoderofCode :) – Avidan Mar 12 '15 at 14:43
  • 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
  • @TabAlleman Nope bro. It's not a duplicate of that. The above link was already posted and removed from comments :) – Avidan Mar 12 '15 at 14:45

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
  • 12,100
  • 1
  • 13
  • 21