2

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 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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Petr
  • 1,128
  • 2
  • 14
  • 23
  • @mu is too short By horizontal I mean "next to each other" rather than "on the top of each other". I need two short columns in the result set rather than one long... – Petr May 11 '11 at 22:08
  • 2
    That sounds exactly like a standard join and proper indexing should make that fast. – mu is too short May 11 '11 at 22:15
  • @mu is too short rowid should be always indexed, but it is still way slower (50%) than two separate SELECTs. – Petr May 11 '11 at 23:08

1 Answers1

0

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.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710