Deleted contacts in AR Customer still appearing in Table

Summary:

Hi, I have created AR Customer and Customer Account, Site and Contact. When checking in Table using below query, it is working as expected.

select * from HZ_RELATIONSHIPS rel
where 1=1
and rel.relationship_code='CONTACT_OF'
and rel.object_id = (
select p.party_id from hz_parties p
where 1=1
and p.party_name = :p_party_name )

But, When I delete the contact in Site and when I run the same query, it is still showing the records. The records still persists even after deleting the contacts.

If it is soft delete, what is the table field to be checked?