SQLITE: horizontally combining large sql tables based on common entry-CodePudding

I have multiple tables I need to join horizontally based on a common entry. For the first sets of tables, I need to join tables that look like:

Table 1 dimension = 1093367x18 and It looks like

ROW #	ID	TEMPERATURE	DESCR	...	NUMB
1	32	23	Y	...	23
2	47	54	N	...	24
...	...	...	...	...	...
1,093367	78	12	Y	...	45

Table 2 dimension = 1093367x648

ROW #	ID	COLOR 1	COLOR 2	...	COLOR 648
1	32	RED	BLUE	...	GREEN
2	47	BLUE	PURPLE	...	RED
...	...	...	...	...	...
1,093367	78	YELLOW	RED	...	BLUE

And I need [Table 1 |Table 2]:

ROW #	ID	TEMPERATURE	DESCR	...	NUMB	COLOR 1	COLOR 2	...	COLOR 648
1	32	23	Y	...	23	RED	BLUE	...	GREEN
2	47	54	N	...	24	BLUE	PURPLE	...	RED
...	...	...	...	...	...	...	...	...	...
1,093367	78	12	Y	...	45	YELLOW	RED	...	BLUE

Is this possible to do in SQLITE? I have only found solutions in which I would have to type out all 648 columns for table 2. Is this the only way to do this in SQLITE?

CodePudding user response：

You don't have to write any column name in the SELECT statement if you do a join with the USING clause instead of the ON clause:

SELECT *
FROM Table1 INNER JOIN Table2
USING(id);

This will return all the columns of the 2 tables (first Table1's rows and then Table2's rows) but the columns used in the USING clause (in this case the column id on which the join is based) will be returned only once.

You can find more about the USING clause in Determination of input data (FROM clause processing).

See a simplified demo.