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.

Answer 1

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;

Answer 2

You can do this (using example tables to simplify),

table1 is,

col1  |  col2  |  col3

table2 is,

col3  |  col4  |  col5

Now perform the union,

select col1, col2, col3, '' as col4, '' as col5, from table1
union
select '' as col1, '' as col2, col3, col4, col5 from table2

Of course, you'll get empty values from columns col4 and col5 when the source row was from table1, and empty values for col1 and col2 when the source row was from table2.

Substitute whatever default value you want for the empty values. My example uses the empty string, but you could also use 0, null, whatever.