join - 水平 UNION ALL

Question

我有两个表，我需要从每个表中选择一列。
这必须在单个查询中完成。
好消息是这两列以正确的方式排序，并且它们都包含相同数量的行。
现在，我知道我可以通过 rowid 加入两个表，但它很慢，因为它必须进行比较。在我的情况下，没有必要......我需要更像水平 UNION ALL 来连接两列长度相等的列。

在 SQLite 3 中是否有可能发生这样的事情？

谢谢。

表 1:

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

表2:

| temperature |
| 1000000000  |
| 2000000000  |

所需的选择输出

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

查询 :

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

这在我的测试应用程序中大约需要 0.75 秒。当我稍后在程序中执行两个单独的 SELECT 并加入输出时，大约需要 0.4 秒，但这不是很方便。最快的方法(~0.23s)是将两列都放在一个表中，但这很浪费，因为我有多个版本的 TABLE2 共享相同的时间戳。

Answer 1

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.