title: SQL - JOIN BETWEEN vs WHERE BETWEEN
body: <p>A few weeks ago I did a query (can't remember what for) and it involved the <code>BETWEEN</code> operator. At first, I used this in the <code>WHERE</code> 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 <code>JOIN</code> clause.</p>
  
  <p>e.g.</p>
  
  <p>"Original" query:</p>
  
  <pre><code>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*/
  </code></pre>
  
  <p>Revised query:</p>
  
  <pre><code>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*/
  </code></pre>
  
  <p>The <code>JOIN</code> version returned a smaller result set compared to the <code>WHERE</code> version, and the <code>JOIN</code> version produced the correct result set whereas <code>WHERE</code> one didn't.</p>
  
  <p>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. </p>
  
  <p>From what I think as to why one produced something different to the other is that the <code>WHERE</code> clause version only <em>selected</em> a range of data, whereas the <code>JOIN</code> version looked for a range of data to <em>join</em> 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).</p>
  
  <p><strong>EDIT</strong>
  I disagree with the possible duplicate because my one looks specifically at the <code>BETWEEN</code> clause, not joins in general. I have read the mentioned post before and it didn't answer my question, hence why I posted this.</p>