How to union all common columns from every table in SQLite

Question

For tables
t1:

C1 C2 C3
1  2  3
4  5  6

table t2:

C1 C2 C4
33 44 55

What query should be written to do a union of all common columns so that the result will be this:

C1  C2
1   2
4   5
33  44

Important to note that I'm not looking for a solution for only two tables but for a solution that does this for every table in the database. DB is SQLite

Answer 1

SQLite does not support dynamic sql, so the most that you can do by using its own capabilities is construct a SQL statement that would do what you want and you can execute it by using a programming language like Java or Python.

This query:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT pti.name col
FROM tables t CROSS JOIN pragma_table_info(t.name) pti
GROUP BY col
HAVING COUNT(*) = (SELECT COUNT(*) FROM tables);

returns all the columns that are common in all tables of the databse.

By using GROUP_CONCAT() you can get all these columns as a comma separated list which you can use in a SELECT statement:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(col) columns
FROM (
  SELECT pti.name col
  FROM tables t CROSS JOIN pragma_table_info(t.name) pti
  GROUP BY col
  HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
);

Finally, concatenate the keywords 'SELECT' and 'FROM' and each table's name and with GROUP_CONCAT() once more and 'UNION ALL' (or 'UNION') as separator construct the sql statement:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(sql, ' UNION ALL ') sql
FROM ( 
  SELECT 'SELECT ' || 
    (
      SELECT GROUP_CONCAT(col) columns
      FROM (
        SELECT pti.name col
        FROM tables t CROSS JOIN pragma_table_info(t.name) pti
        GROUP BY col
        HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
      )
    ) || ' FROM ' || name AS sql
  FROM tables
);

This will return a string like:

SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SELECT col1,col2 FROM table3

which you can execute.

See a simplified demo.