Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: help with exclusion join

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dtrobert View Post
    What I came up with works but isn't very efficient I feel
    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.

    Code:
    select * from phones p
    where p.name not in (select cr.name from callrecord cr)
    Note that this will return wrong results if cr.name can be NULL!

    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.
    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.

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    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?

  4. #4
    Join Date
    Feb 2010
    Posts
    40
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •