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!