# postgreSQL - join lateral
###### tags: `sql`

--init tmp1
drop table if exists tmp1;
create temp table tmp1(name varchar, data int);
insert into tmp1(name,data )
values('report1', 10),('report2',20);
select * from tmp1;
-- init tmp2
drop table if exists tmp2;
create temp table tmp2(name varchar, data int);
insert into tmp2(name,data )
values('a', 10),('b',5),('c',15),('d',16),('e',3),('f',22);
select * from tmp2;

-- judge function
create or replace function func1(t2 int, t1 int)
returns boolean
as $$
declare
    result boolean;
begin
    if t2<t1 then
        return True;
    else
        return False;
    end if;
end
$$ language plpgsql;

-- join query
select 
t1.name
,t1.data
,t2.name
,t2.data
from tmp1 t1
left join lateral(
    select
    *
    from tmp2 
    where func1(tmp2.data,t1.data)
)t2 on True;

可以使用自定義function來判斷是否join

1. 不是所有join都可以使用join lateral
2. postgreSQL 叫做 join lateral, 其他SQL可能名稱不一樣