PostgreSQL 测试 left join 条件带有null值时的效果

tempa表

tempb表

1. tempa left join tempb 关联查询 需求新增工单流水号相同的记录

select * from tempa a left join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号";

 

2. 加上 where a."需求新增工单流水号" is not null; 作为条件

select * from tempa a left join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号" where a."需求新增工单流水号" is not null;

 

3. 加上 and a."需求新增工单流水号" is not null; 作为条件

 

select * from tempa a left join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号" and a."需求新增工单流水号" is not null;

 

4. 加上 where b."需求新增工单流水号" is not null; 作为条件

select * from tempa a left join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号" where b."需求新增工单流水号" is not null;

 

5. 加上 and b."需求新增工单流水号" is not null 作为条件

select * from tempa a left join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号" and b."需求新增工单流水号" is not null;

 

6. left join 改为用 inner join

select * from tempa a inner join tempb b on a."需求新增工单流水号"=b."需求新增工单流水号";

空行都消失了，不会匹配上