CODEWITHSUNDEEP
sqlsqlite
lambdakappatheta
lambdakappatheta

Reputation: 305

Combine arbitrary tables without knowing their column names

I am looking for a way to combine two tables like so:

x y
1 2
x z
3 4
x y z
1 2 NULLL
3 NULL 4

Importantly, I would like to be able to combine two arbitrary tables whose column names I do not know in advance. Therefore

SELECT x, y, Null as z FROM t1 
UNION 
SELECT x, Null as y, z FROM t2

doesn't solve my problem even though it does the right thing for this particular example.

EDIT

The problem that motivated this question:

I am building a library that communicates with a data source. It has to parse raw data that comes from the data source and control the data source based on the values it received. Of course, it also has to store the data it received. It should work with any data source as long as the raw results can be turned into a mapping. Adding a new data source can be done by writing a new parser and rewriting the problem specific logic that controls the feedback sent to the data source. The problem is that it cannot be known in advance what keys the mappings produced by the parser will return. And it is also possible that the process doesn't end for quite a while so waiting for all results to come in, examining them and building a table that has all the columns needed is not an option. It is also important to note that it is possible that certain sensors are only activated on rare occasions therefor it is possible that new keys will show up in the mappings even after a substantial amount of time. While, in general, a new key could appear anytime, in certain cases it is possible to know that the next x mappings will have the same keys. In this case, a temporary table could be built and concatenated to a main table like I described in this question. I'm in favor of using one main table instead of scattering data across multiple tables because one table seems to be the easiest for analysts to work with.

Upvotes: 0

Views: 495

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270311

I cannot think of a way of doing this in SQLite. But in generic SQL, you could do this if you knew columns in common. The key idea is that the using clause would eliminate duplicates. Then, if you use join you can get all the columns from both tables.

This would look like:

select *
from t1 left join
     (select t2.*
      from t2
      where 1 = 0    -- select no rows!
     ) t2
     using (x)
union all
select *
from (select t1.*
      from t1
      where 1 = 0
     ) t1 right join
     t2
     using (x);

You need to include all common columns in the using clause so they are not duplicated in the result set. The * takes care of that when using using.

This requires right join, which SQLite does not support. This is one of the few cases where it cannot be replaced by left join, because the ordering of the columns using * is important.

Although interesting as a thought exercise, such code should not find its way into production code. If you really needed to do something like this, look up the columns in the metadata tables and construct the correct query.

Upvotes: 1

Related Questions