title: SELECT union of results from same subquery in MySQL
body: <p>Say I have a query which looks something like this:</p>
  
  <pre><code>select step0.a, step1.a, step2.a
  from (select id from tbl1) as step
  inner join tbl1 as step0 on step0.id = step.id
  left join tbl1 as step1 on step1.b = step0.a
  left join tbl1 as step2 on step2.b = step1.a
  </code></pre>
  
  <p>This works fine. Now, say I want to union these results with the reverse of <code>a</code> and <code>b</code>, i.e.:</p>
  
  <pre><code>select * from (
  (
  select step0.a, step1.a, step2.a
  from (select id from tbl1) as step
  inner join tbl1 as step0 on step0.id = step.id
  left join tbl1 as step1 on step1.b = step0.a
  left join tbl1 as step2 on step2.b = step1.a
  )
  union (
  select step0.b, step1.b, step2.b
  from (select id from tbl1) as step
  inner join tbl1 as step0 on step0.id = step.id
  left join tbl1 as step1 on step1.a = step0.b
  left join tbl1 as step2 on step2.a = step1.b
  )
  ) rows
  </code></pre>
  
  <p>This also works fine. But notice that the <code>select id from tbl1</code> subquery is duplicated.</p>
  
  <p>The question I have, is how do I store the results of this subquery, without using temporary tables, such that each <code>select</code> in the union can reference it?</p>