The efficient way to use left join to find the closest value

Question

I need to find the closest value in the right table and combine them all. but for doing do my left join query runs on all the permutations and it tasks a lot of resources to calculate (my basic tables are huge)

For example my A table looks like 
<A,1>
<A,2>
<A,10>

And table B looks like : 

<A,4>
<A,5>
<A,6>
<A,7>

For this example the result will be: 

<A,1,4>
<A,2,4>
<A,10,7>

This how I thought to do so:

select * from (
select *,row_number() over(partition by rown order by abs(b-a) asc)  diff  from (
(select a,b, row_number over () rown from x) a
CROSS JOIN
(select a,b from x) b
on a.a = b.a

) )where diff =1

Is there a better and efficient way to do so?

Answer 1

Consider below example

select id, a.val a_val, b.val b_val
from tableA a
left join tableB b
using(id)
where true 
qualify row_number() over(partition by id, a.val order by abs(a.val - b.val)) = 1

If applied to sample data in your question - output is