title: Reverse join in MySQL
body: <p>SO,</p>
  
  <p><strong>The problem</strong></p>
  
  <p>My question is about - how to join table in MySQL with itself in reverse order? Suppose I have:</p>
  
  <pre>
  id  name
  1   First
  2   Second
  5   Third
  6   Fourth
  7   Fifth
  8   Sixth
  9   Seventh
  13  Eight
  14  Nine
  15  Tenth
  </pre>
  
  <p>-and now I want to create a query, which will return joined records in reverse order:</p>
  
  <pre>
  left_id name    right_id    name 
     1    First      15   Tenth 
     2    Second     14   Nine 
     5    Third      13   Eight 
     6    Fourth      9   Seventh 
     7    Fifth       8   Sixth 
     8    Sixth       7   Fifth 
     9    Seventh     6   Fourth 
    13    Eight       5   Third 
    14    Nine        2   Second 
    15    Tenth       1   First 
  </pre>
  
  <p><strong>My approach</strong></p>
  
  <p>I have now this query:</p>
  
  <pre><code>SELECT 
    l.id AS left_id, 
    l.name, 
    (SELECT COUNT(1) FROM sequences WHERE id&lt;=left_id) AS left_order, 
    r.id AS right_id,
    r.name,
    (SELECT COUNT(1) FROM sequences WHERE id&lt;=right_id) AS right_order 
  FROM 
    sequences AS l 
    LEFT JOIN 
      sequences AS r ON 1
  HAVING
    left_order+right_order=(1+(SELECT COUNT(1) FROM sequences));
  </code></pre>
  
  <p>-see this <a href="http://sqlfiddle.com/#!2/55b80/1" rel="noreferrer">fiddle</a> for sample structure &amp; code.</p>
  
  <p><em>Some background</em></p>
  
  <p>There's no use case for that. I was doing that in application before. Now it's mostly curiosity if there's a way to do that in SQL - that's why I'm seeking not just 'any solution' (like mine) - but as simple as possible solution. Source table will always be small (&lt;10.000 records) - so performance is not a thing to care, I think.</p>
  
  <p><strong>The question</strong></p>
  
  <p>Can my query be simplified somehow? Also, it's important not to use variables. Order could be included in result (like in my fiddle) - but that's not mandatory.</p>