left semi join 的結(jié)果只返回左表的列，右表只能放在 join condition 中用于過濾判斷，可以理解為 in 的替代品

select  *
from a left semi join b
on a.id = b.id

--- 等價(jià)于

select * 
from a 
where a.id in 
(select b.id from b)

left anti join 與 left semi join 相反，返回不滿足 join condition 的結(jié)果，可以理解為 not in 的替代品

select  *
from a left anti join b
on a.id = b.id

--- 等價(jià)于

select * 
from a 
where a.id not in
(select b.id from b)