How to union all common columns from every table in SQLite-CodePudding

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:

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

CodePudding user response：

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.