It would require testing, but that may execute "faster" than the CTE because it only scans the table t1 once. Here it is "more pretty":
```
select id as id1,
(
select id2
from (
select id2,
delta
from (
select t2.id as id2,
t2.ts-t1.ts as delta
from t2
where t2.ts >= t1.ts
order by t2.ts
limit 1
)
union all
select id2,
delta
from (
select t2.id as id2,
t1.ts-t2.ts as delta
from t2
where t2.ts <= t1.ts
order by t2.ts desc
limit 1
)
order by delta
limit 1
)
) as id2
from t1
;
```
Note that it might be even more efficient to replace the `union all` with a regular `union` and remove the `order by` since "union" will use a b-tree for duplicate detection resulting in an ordered result (though this is perhaps implementation detail). Though I think then you have to make sure to exclude NULLs from the union.
```
select id as id1,
(
select id2
from (
select delta,
id2
from (
select t2.id as id2,
t2.ts-t1.ts as delta
from t2
where t2.ts >= t1.ts
order by t2.ts
limit 1
)
union
select delta,
id2
from (
select t2.id as id2,
t1.ts-t2.ts as delta
from t2
where t2.ts <= t1.ts
order by t2.ts desc
limit 1
)
)
) as id2
from t1
;
```
NB: Edited to change column order in union.
Also note that in the case of an equidistant before/after timestamps, the t2 row with the lower id will be chosen.
Removed the NULL check because no null rows will be generated.