title: How to join two tables with the same number of rows in SQLite?
body: <p>I have almost the same problem as described in <a href="https://stackoverflow.com/questions/793174/how-to-join-two-tables-together-with-same-number-of-rows-by-their-order">this question</a>. I have two tables with the same number of rows, and I would like to join them together one by one.</p>
  
  <p>The tables are ordered, and I would like to keep this order after the join, if it is possible.</p>
  
  <p>There is a rowid based solution for MSSql, but in SQLite rowid can not be used if the table is coming from a WITH statement (or RECURSIVE WITH).</p>
  
  <p>It is guaranteed that the two tables have the exact same number of rows, but this number is not known beforehand. It is also important to note, that the same element may occur more than twice. The results are ordered, but none of the columns are unique.</p>
  
  <p>Example code:</p>
  
  <pre><code>WITH
  table_a (n) AS (
    SELECT 2
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
  ),
  table_b (s) AS (
    SELECT 'valuex'
    UNION ALL
    SELECT 'valuey'
    UNION ALL
    SELECT 'valuez'
  )
  SELECT table_a.n, table_b.s
  FROM table_a
  LEFT JOIN table_b ON ( table_a.rowid = table_b.rowid )
  </code></pre>
  
  <p>The result I would like to achieve is:</p>
  
  <pre><code>(2, 'valuex'),
  (4, 'valuey'),
  (5, 'valuez')
  </code></pre>
  
  <p>SQLFiddle: <a href="http://sqlfiddle.com/#!5/9eecb7/6888" rel="nofollow noreferrer">http://sqlfiddle.com/#!5/9eecb7/6888</a></p>