mysql - SQL calculate the number of row in a specific range -

there's 2 table in database:

table a

column_a1 column_a2  a1         10  a2         20  a3         30

table b

column_b1 column_b2  b1         11  b2         21  b3         31  b4         29  b5         30.5

i want calculate how many row of table b match following condition:

range: a1±1, a2±1, a3±1, ...

for example:

b1∈[a1-1,a1+1]
count these row, return value 1.

b2∈[a2-1,a2+1]
count these row, return value 1.

b3∈[a3-1,a3+1]
b4∈[a3-1,a3+1]
b5∈[a3-1,a3+1]
count these row, return value 3.

result should this:

column_a1 column_a2 num_match  a1         10        1  a2         20        1  a3         30        3

it's easy use loop in other programming language, what's simplest way make in sql ? thanks.

i correlated subquery:

select a.*,        (select count(*)         b         b.column_b2 between a.column_a2 - 1 , a.column_a2 + 1        ) num_match a;

note: between used suggesting bounds included in range. if not intention, use explicit < , > logic.

many databases able take advantage of index on b(column_b2) query. can test on mysql see if case.

Brent