how to check two database table for missing information.

Question

i have two tables both have columns of fname and lname how would i write the query to check and see if one of the tables is missing a record that doesnt exist in the other table?

here are the tables and its columns

tbl_client
-fname
-lname

tbl_lease
-fname
-lname

i need to keep checking the tbl_lease to see if the records match the table tbl_client and if it doesnt i would be able to display it to me. thanks

Answer 1

A simple way would be to use a not exists predicate in the where clause:

select tbl_client.fname, tbl_client.lname, 'missing from lease' as missingfrom
    from tbl_client
    where not exists
       (select 1 from tbl_lease where tbl_lease.fname=tbl_client.fname
        and tbl_lease.lname=tbl_client.lname)
union
select tbl_lease.fname, tbl_lease.lname, 'missing from client' as missingfrom
    from tbl_lease
    where not exists
       (select 1 from tbl_client where tbl_lease.fname=tbl_client.fname
        and tbl_lease.lname=tbl_client.lname)

Or maybe do a full outer join and check which table's records are null, but that's probably a bit less straightforward, and it could be less efficient than a not exists.

Answer 2

Get all the records in tbl_lease where fname is not present in tbl_client:

 select tl.* from tbl_lease tl
    left join tbl_client tc on tl.fname = tc.fname 
 where tc.fname is null;

And the other way around, records present in tbl_client where the fname is not present in tbl_lease

 select tc.* from tbl_client tc
   left join tbl_lease tl on tc.fname = tl.fname 
  where tl.fname is null;