title: UNION ALL in CTE
body: <p>I'm trying to get a UNION ALL working with a CTE which I'm using for paging. I need to get all records matching a set of criteria from two tables, then page the results. The first table's CTE looks like this:</p>
  
  <pre><code>;WITH Results_CTE AS (SELECT t1.SomeIntKey1, ROW_NUMBER() OVER (ORDER BY SomeIntKey1) AS RowNum  
   FROM Table1 t1
   LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey1
   WHERE Postcode LIKE 'CHX 1XX%' 
   ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey1 = d.Key WHERE RowNum &gt;= 0 AND RowNum &lt;= 10 OPTION(RECOMPILE) 
  </code></pre>
  
  <p>The second table's paging SQL (which works fine) is:</p>
  
  <pre><code>;WITH Results_CTE AS (SELECT t2.SomeIntKey2, ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
   FROM Table2 t2
   LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey2
   WHERE Postcode LIKE 'CHX 1XX%' 
   ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key WHERE RowNum &gt;= 0 AND RowNum &lt;= 10 OPTION(RECOMPILE) 
  </code></pre>
  
  <p>For the combined paged data, I've tried something like:</p>
  
  <pre><code>;WITH Results_CTE AS (SELECT t2.SomeIntKey2, ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
   FROM Table2 t2
   LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey2
   WHERE Postcode LIKE 'CHX 1XX%' 
  UNION ALL 
  SELECT t1.SomeIntKey1
   FROM Table1 t1
   LEFT JOIN CalculatedData d ON d.Key = t1.SomeIntKey1
   WHERE Postcode LIKE 'CHX 1XX%' 
   ) SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key WHERE RowNum &gt;= 0 AND RowNum &lt;= 10 OPTION(RECOMPILE) 
  </code></pre>
  
  <p>However, this results in an error:
  <code>All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.</code></p>
  
  <p>I know UNION ALLs can be confusing at the best of times, especially with joins, but I'm essentially getting a list of INT keys from two tables, then joining them to a third which contains the data I need (keys from both tables will be present in the joined column on the <code>Data</code> table.</p>