Латеральное объединение (LATERAL JOIN)

Ну вот, хоть в чем-то MySQL переплюнул MariaDB и это LATERAL (на дворе осень 2020)

create table t1(id int, val int);
create table t2(id int, val int);

insert into t1(id, val)
with recursive
r1 as (
  select 1 id, (rand()*100 div 1) r 
  union all
  select r1.id+1 id, (rand()*100 div 1) r from r1 
   where r1.id<100
 ) 
  select id, r from r1
  ;
  
insert into t2(id, val)
with recursive
r1 as (
  select 1 id, (rand()*100 div 1) r 
  union all
  select r1.id+1 id, (rand()*100 div 1) r from r1 
   where r1.id<100
 ) 
  select id, r from r1 where r between 40 and 60
 ;  
 
 select t1.*, t22.* from t1
  join lateral (
  select max(t2.id) ma, min(t2.id) mi from t2 where t1.val between t2.val-10 and t2.val+10
 ) t22 on (true) -- по сути своей left join ибо "on (true)"
-- ) t22 on (t22.ma is not null) -- как костыль inner join можно получить так
 ;