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

user_3632673 0
Viewed 298

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);
left-join sqlite sql
3 Answers

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

Variation that uses the window functions added in Sqlite 3.25 instead of a correlated subquery:

WITH cte(id, value, id2, rnk) AS
 (SELECT t1.id, t1.value, t2.id
       , rank() OVER (PARTITION BY t1.id ORDER BY abs(t1.value - t2.value))
  FROM t1
  LEFT JOIN t2)
SELECT id, value, id2
FROM cte
WHERE rnk = 1
ORDER BY id;

which gives

id          value       id2       
----------  ----------  ----------
1           1           1         
2           1           1         
3           2           2         
4           12          3         
5           13          3
user_99521960

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;
user_11440350
Related