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

朱雀 2022-12-13 12:40 211阅读 0赞

tempa表

![20201011143412410.png][]

tempb表

![20201011143422933.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70][]

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

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

![20201011143716313.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1][]

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

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

![20201011144206470.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 2][]

6. left join 改为用 inner join

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

![20201011201506194.png][]

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

[20201011143412410.png]: /images/20221123/07d478677773491fa248f1b1dc0f5f64.png
[20201011143422933.png]: /images/20221123/d4a75b010445436cb58a7b3d25ae9f4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70]: /images/20221123/983c4308f133441f8cf38d808dcb518b.png
[20201011143716313.png]: /images/20221123/3c488f44c9e04dd8aad0f1267fffff3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1]: /images/20221123/99992b6e8b924c3483ce97f4b2196e3d.png
[20201011144206470.png]: /images/20221123/cbc1530aed7f4f71a53ee65a790ccdff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 2]: /images/20221123/b053b1fd549d4ba5877e49b602b45ee1.png
[20201011201506194.png]: /images/20221123/6bd69c0dc4c0459ba593b344c277df7b.png