horizontal UNION ALL

I have two tables and I need to select one column from each of them. This must be done in a single query. The good news is that the two columns are ordered the right way and they both contain the same number of rows. Now, I know I could JOIN the two tables by rowid, but it is开发者_JAVA百科 slow as it has to do that comparison. In my case it is not necessary... I need something more like horizontal UNION ALL to concatenate two columns of equal length.

Is anything like that possible in SQLite 3?

Thanks.

TABLE1:

| timestamp | FIELD1 | FIELD2 | ...
| 12345678  | 000000 | 000000 | ...
| 00154789  | 000000 | 000000 | ...

TABLE2:

| temperature |
| 1000000000  |
| 2000000000  |

REQUIRED SELECT OUTPUT

| timestamp | temperature |
| 12345678  | 1000000000  |
| 00154789  | 2000000000  |

QUERY:

SELECT timestamp, temperature
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.rowid = TABLE2.rowid;

This takes ~0.75s in my testing app. When I do two separate SELECTs and join the outputs later in my program it takes ~0.4s, but it is not very convenient. The fastest way (~0.23s) is to have both columns in one table, but it is wasteful as I have multiple versions of TABLE2 that share the same timestamps.

---

SQLite supports UNION ALL.

Two or more simple SELECT statements may be connected together to form a compound SELECT using the UNION, UNION ALL, INTERSECT or EXCEPT operator. In a compound SELECT, all the constituent SELECTs must return the same number of result columns. As the components of a compound SELECT must be simple SELECT statements, they may not contain ORDER BY or LIMIT clauses. ORDER BY and LIMIT clauses may only occur at the end of the entire compound SELECT.

A compound SELECT created using UNION ALL operator returns all the rows from the SELECT to the left of the UNION ALL operator, and all the rows from the SELECT to the right of it. The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set. The INTERSECT operator returns the intersection of the results of the left and right SELECTs. The EXCEPT operator returns the subset of rows returned by the left SELECT that are not also returned by the right-hand SELECT. Duplicate rows are removed from the results of INTERSECT and EXCEPT operators before the result set is returned.