PostgreSQL , 关系代数 , EquiJoin , SemiJoin , AntiJoin , Division

原文地址：https://yq.aliyun.com/articles/501423

JOIN本身也分好多种比如EquiJoin , SemiJoin , AntiJoin , Division。

EquiJoin

这种JOIN最为常见。例如：

select a.* from a join b on (a.xx = b.xx);  

实际上关系代数中为θ-join，包括(<, ≤, =, >, ≥)，当使用=时，对应的就是equijoin.

只要操作符（JOIN条件）返回TRUE，就输出对应的JOIN记录。（也可以理解为笛卡尔乘积中，仅返回JOIN条件为TRUE的那些）

SemiJoin

返回在Employee中的记录，同时这条记录与Dept中的所有记录一对多操作时，有一个返回TRUE的操作即可。

例如

select * from Employee where exists   
  (select 1 from Dept where Employee.DeptName = Dept.DeptName);  -- 现实中操作符可以随意替代，代表不同语义  

由于semiJoin的操作在EXISTS中只要有一条符合TRUE即可，所以很大概率下并不需要扫描全量Dept。

semiJOIN支持hash, merge, nestloop几种JOIN方法。

Employee很小，并且Dept有索引时，NESTLOOP就会比较快。

Employee很大时，使用hash就很快。

AntiJoin

AntiJoin与SemiJoin表达的意思有点相反，要求Employee中的每一条记录，与Dept中所有记录进行操作后，Dept中没有任何一条能满足。返回在Employee中的这样的记录。

例如

select * from Employee where not exists   
  (select 1 from Dept where Employee.DeptName = Dept.DeptName);   -- 现实中操作符可以随意替代，代表不同语义  

 

AntiJoin要求Employee中每一条记录与Dept所有记录进行操作，并且所有操作都不满足条件，这条算作有效记录，返回该Employee的记录。

对于JOIN操作符为=号的，不管是semijoin还是antijoin，都可以用HASH join，达到非常好的加速效果。

 

Division

JOIN中的除法运算，没有对应的SQL，需要写多条SQL或者使用CTE语法写一条SQL来实现。

、补齐

tmp1:

select Student, Task from  
(  
  select distinct Student from Completed  
) t1   
,  
(  
  select Task from DBProject  
) t2;  

2、使用AntiJoin计算余数

tmp2:

select Student from Completed where not exists   
  (select 1 from tmp1 where tmp1.Student=Completed.Student and tmp1.Task=Completed.Task);  

3、去重，并使用except求差，得到最终结果

select distinct Student from Completed   
except  
select Student from tmp2;