How to left-join two tables based on the closest value of their joining attributes?

Question

Suppose I have two tables, t1 and t2, they look as follows:

t1:

id, value
1, 1
2, 1
3, 2
4, 12
5, 13

t2:

id, value
1, 1
2, 2
3, 10

I'm trying to left-join the two tables based on the minimum difference between t1.value and t2.value, such that the result contains all records of t1 with their closest matching partner from t2, so

t1.id, t1.value,t2.id
1,1,1
2,1,1
3,2,2
4,12,3
5,13,3

I suppose the query would look something like this:

SELECT t1.id, t1.value, t2.id
FROM t1 LEFT JOIN t2
ON t1.value = t2.value -- I don't know what to do here

Here are the SQLite queries to reproduce the tables:

CREATE TABLE "t1" ( "id" INTEGER, "value" INTEGER);
CREATE TABLE "t2" ( "id" INTEGER, "value" INTEGER);
INSERT INTO t1 VALUES (1, 1), (2, 1), (3, 2), (4, 12), (5, 13);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 10);

Answer 1

You can do it like this:

SELECT t1.id, t1.value, t2.id
FROM t1 LEFT JOIN t2
ON abs(t1.value - t2.value) = (select min(abs(t1.value - value)) from t2)

You join on the absolute minimum of t1.value - t2.value.
See the demo.
Results:

| id  | value | id  |
| --- | ----- | --- |
| 1   | 1     | 1   |
| 2   | 1     | 1   |
| 3   | 2     | 2   |
| 4   | 12    | 3   |
| 5   | 13    | 3   |

Answer 2

I would recommend a correlated subquery:

select t1.*,
       (select t2.value
        from t2
        where t2.id <= t1.id
        order by t2.id desc
        limit 1
       ) as t2_value
from t1;