SQLite Union of n tables with m columns

Question

I have this problem. There are n tables created dynamically and each table has m columns, the columns could be repeated. This tables have in common 2 columns but there is no related data between them, for example: Table1 | A | B | Col1 | Col2 |

    Table2

| A | B | Col3 | Col4 |

    Table3

| A | B | Col1 | Col2 | Col4 |

What I want to do is to merge all the tables into a big one like this:

    BigTable

| A | B | Col1 | Col2 | Col3 | Col4 |

And all the rows concatenated, for example if in table1 rows = 5, table2 rows = 3, table3 rows = 2, the big table will have 10 entries.

I can accomplish this by using a query like this:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION
SELECT A, B, null as Col1, null as Col2, Col3, Col4 FROM Table2
UNION
SELECT A, B, Col1, Col2, null as Col3, Col4 FROM Table3

But I want to know if there is a better way to do this, because there will be more columns and more tables, and there is the possibility that all the columns are different.

if table1 had 3 rows and table2 had 3 rows, how many rows would bigtable have? — Jeffrey Blattman

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-08-02T16:54:55

The only improvement to your query is to use union all instead of union. Only use union if you explicitly want to remove duplicates, because it always attempts to:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION ALL
SELECT A, B, null as Col1, null as Col2, Col3, Col4 FROM Table2
UNION ALL
SELECT A, B, Col1, Col2, null as Col3, Col4 FROM Table3;

EDIT:

You can further simplify this to:

SELECT A, B, Col1, Col2, null as Col3, null as Col4 FROM Table1
UNION ALL
SELECT A, B, null, null, Col3, Col4 FROM Table2
UNION ALL
SELECT A, B, Col1, Col2, null, Col4 FROM Table3;

The column names are only used for the first select in the union all. After that, the columns are identified by position.

EDIT II:

There is a trick that you can use to get "logical" matches on union all. I don't particularly like it, but you don't have to list the columns for all the subqueries. However, the select is more complicated, and it has another subquery, and you still need subqueries:

select coalesce(t1.A, t2.A, t3.A) as A,
       coalesce(t1.B, t2.B, t3.B) as B,
       coalesce(t1.Col1, t2.Col1, t3.Col1) as col1,
       coalesce(t1.Col2, t2.Col2, t3.Col2) as col2,
       coalesce(t1.Col3, t2.Col3, t3.Col3) as col3
from (select 'Table1' as tablename union all
      select 'Table2' union all
      select 'Table3'
     ) driver left outer join
     (select t.*, 'Table1' as tablename
      from Table1
     ) t1
     on t1.tablename = driver.tablename left outer join
     (select t.*, 'Table2' as tablename
      from Table2
     ) t2
     on t2.tablename = driver.tablename left outer join
     (select t.*, 'Table3' as tablename
      from Table3
     ) t3
     on t3.tablename = driver.tablename;

SQLite Union of n tables with m columns

2 Answers