DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004

    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
    (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
    Geneva Switzerland
    Is that any quicker than the simple solution (approx):
    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
    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 
    (select orderidpartqty 
        from tblorder1 t1
         where orderid not in 
    (select id from tblorder2 where qty 0)
    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
    Another approach would be using minus

    select col1 alias1, col2 alias2
    from table1
    where something = nothing
    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.