MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


@@version


8.0.22


create table A(puid int, pid int,systemcode char(10));
insert into A values(100,1003,'RG'),(200,1006,'CA'),(200,1007,'CA'),(300,1008,'RG');

create table B(puid int, pid int,systemcode char(10));
insert into B values(100,1003,'RG'),(100,1004,'RG'),(100,1005,'RG'),(200,1006,'CA'),
(300,1009,'RG')
select * from B


puid
pid
systemcode


100
1003
RG

100
1004
RG

100
1005
RG

200
1006
CA

300
1009
RG


select 1
                  from B join
                       A a2
                       on a2.puid = B.puid where B.pid = a2.pid


1


1

1


select A.*
from A
where not exists (select 1
                  from B join
                       A a2
                       on a2.puid = B.puid
                  where B.pid = a2.pid
                 );
select * from A 
 where not exists (select 1 from B where A.puid=B.puid 
group by B.puid having count(B.pid)=1)


puid
pid
systemcode


100
1003
RG


select A.* from A
join
(
select A.puid
from A left join B on A.puid=B.puid and A.pid=B.pid
group by A.puid having sum(case when B.pid is null then 0 else 1 end)=0
)X on A.puid=X.puid


puid
pid
systemcode


300
1008
RG