finding records in one table but not the other
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: finding records in one table but not the other

  1. #1
    Join Date
    Aug 2004
    Posts
    1

    finding records in one table but not the other

    I am writing some queries which are designed to return rows from one table which have no matching records in the other.

    The following takes around half a minute or so and is the best solution I've come up with so far:

    select a.orderid,a.part,a.qty,b.partflag from
    (select orderid,part,qty from tblorder1 where qty > 0
    and not exists
    ( select id from tblorder2 where qty > 0
    and orderid = id
    )a,
    (select part,partflag from iteminfo)b
    where a.part = b.part

    The 2 main subsections return around 600-700 records each.

    Is there any speedier way of obtaining records in the first table but not the other ?
    I've tried outer join format and that's even slower.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Is that any quicker than the simple solution (approx):
    Code:
    select a.orderid, a.part, a.qty, b.partflag
    from tblorder1 a, iteminfo b
    where a.part = b.part
    and   a.qty > 0
    and not exists
    (select id from tblorder2 c
     where c.qty > 0
     and   a.orderid = c.id)

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    sure would be easier if you used table aliases everywhere.
    anyways ...

    1. What indexes do you have on the tables?
    2. What is the existing PLAN for the query?
    3. is tblorder2.id a NOT NULL field?
    4. If orderid = id can you have a tblorder2.qty > 0 but the tblorder1.qty = 0?

    without this knowledge my first pass is:
    PHP Code:
    select a.orderida.parta.qtyb.partflag 
    from
      
    (select orderidpartqty 
        from tblorder1 t1
         where orderid not in 
    (select id from tblorder2 where qty 0)
           and 
    qty 0a,
      
    iteminfo b
    where a
    .part b.part 
    knowing what columns are the PKs and indexes would totally change
    the possible writing of the query.
    - Cookies

  4. #4
    Join Date
    Feb 2004
    Posts
    77
    Another approach would be using minus

    select col1 alias1, col2 alias2
    from table1
    where something = nothing
    minus
    select col3 alias1, col2 alias2
    form table2
    where something = everything

    This may be faster in certain cases because you are not doing a lookup in second table for every record in the first table especially when your where clause may have unindexed criteria only.

Posting Permissions

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



Click Here to Expand Forum to Full Width