title: Mysql Inner join with OR condition?
body: <p>I have 2 tables like below</p>
  
  <p><strong>location_distance</strong></p>
  
  <pre><code>----------------------------------------------
  id   | fromLocid    | toLocid   |  distance
  ----------------------------------------------
  1    |  3           |  5        |   70
  2    |  6           |  8        |   15
  3    |  2           |  4        |   63
  ...
  </code></pre>
  
  <p><strong>other_table</strong></p>
  
  <pre><code>--------------------------------------------
  Id  | fromLocid   | toLocid    | otherdata
  --------------------------------------------
  12  |  5          | 3          | xxxx
  22  |  2          | 4          | xxxx   
  56  |  8          | 6          | xxxx
  78  |  3          | 5          | xxxx
  </code></pre>
  
  <p>I would like to retrieve the distance b/w the locations in other_table for each row. Here's what i've tried</p>
  
  <pre><code>SELECT ot.*, ld.distance FROM other_table AS ot 
      INNER JOIN location_distance ld ON ld.fromLocid = ot.fromLocid AND ld.toLocid = ot.toLocid
  </code></pre>
  
  <p>This doesnt return the rows if the locations values are vice versa. How can i rewrite the above query to produce expected result? Should i inlude <strong>OR</strong> condition on the join clause? like below?</p>
  
  <pre><code>SELECT ot.*, ld.distance FROM other_table AS ot 
      INNER JOIN location_distance ld ON (ld.fromLocid = ot.fromLocid OR ld.fromLocid = ot.toLocid) AND (ld.toLocid = ot.fromLocid OR ld.toLocid = ot.fromLocid)
  </code></pre>
  
  <p><strong>but this query Explain says "Range checked for each record". .. is this a bad practise?</strong></p>
  
  <p><em>Result</em></p>
  
  <pre><code>--------------------------------------------------------
  Id  | fromLocid | toLocid    | otherdata   | distance
  --------------------------------------------------------
  22  |  2        |   4        | xxxx        | 63
  78  |  3        |   5        | xxxx        | 70
  </code></pre>
  
  <p><em>Expected Result should be</em></p>
  
  <pre><code>-----------------------------------------------------
  Id  | fromLocid   | toLocid   | otherdata  | distance
  -----------------------------------------------------
  12  |   5         |    3      | xxxx       | 70
  22  |   2         |    4      | xxxx       | 63
  56  |   8         |    6      | xxxx       | 15 
  78  |   3         |    5      | xxxx       | 70
  </code></pre>