Hi,
I'm trying to make an efficient query which compares two tables and finds rows which are not in common. The tables are phones and callrecords. I want to see which phones have no entries in the callrecords (not been used in a while).

What I came up with works but isn't very efficient I feel

select * from phones p
where p.name not in (select cr.name from callrecord cr)


I thought perhaps doing a left join and then distinct might help. I don't want the actual callrecords in the result, just the list of phones. Any help is appreciated.

p.s. if I get this working, I was thinking to expand the query to include phones which have old records (calltime < say 30 days). Not sure if that might change the approach.

Thanks

Don't "feel", test it and get a proof.
The execution plan will tell you if it's not efficient or if other statements are more efficient.

Note that this will return wrong results if cr.name can be NULL!

Use EXPLAIN ANALYZE on both statements and you'll see which one is better.

PostgreSQL's optmizier is pretty smart, and I wouldn't be surprised if both versions yield the same execution plan.

You should have an index on phones.name though.

Okay but are these the only two options for finding records in one table which are not in another (distinct join and subselect). Am I missing another option?

I actually found a good page showing the various options here

Finding records in one table not present in another table

for anyone with a similar issue.