Actual practice - search some columns in one table but not that table - 8

最新推荐文章于 2023-10-22 21:50:17 发布

LitteBB

最新推荐文章于 2023-10-22 21:50:17 发布

阅读量552

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/u011894964/article/details/51210172

版权

19 篇文章 0 订阅

订阅专栏

Now take a look at two results :

这里写图片描述

There are Table A and Table B, and I need find some loans which are exist in Table A but not in Table B.

Here are 3 effective ways to achieve this purpose

select zard.loan_no,*
from  msptax tax
left join msphazard zard on tax.loan_no = zard.loan_no
where zard.loan_no is null

select * from msptax where (select count(1) from MSPHazard where msptax.loan_no = MSPHazard.loan_no ) = 0

select * from msptax where not exists (select 1 from MSPHazard where MSPHazard.loan_no = msptax.loan_no )

Another one is to use “Not in”, but it is not recommended due to the low effective to execute.

I think we will often use this kind of scripts to achieve the data we need. Take a note here to remind me!

关注