#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Cool Select records where no records in other select


    Hi,
    I'm wondering if anybody could give me some advise what the best way is to write an sql-select(most performant)

    We have a TABLE_A and a TABLE_B.
    We need to find all the records from table_A which have a specific status that are not found back in TABLE_B.

    select TABLE_A.number from TABLE_A where Status > 100 and status < 10000
    where TABLE_A.number not in (select TABLE_B.number from TABLE_B where (TABLE_B.TYPE=1 or TABLE_B.TYPE =2) and (TABLE_B.ACTION is null or TABLE_B.ACTION > now());

    In TABLE_B there are more than 20000 records. I notice that a select with "NOT IN" or "NOT EXISTS" is not so performant.

    Can anybody advise how to write such a command on a performant way?

    Yours sincerely
    Stefan
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Code:
    SELECT table_a.number 
      FROM table_a 
    LEFT OUTER
      JOIN table_b
        ON table_b.number = table_a.number  
       AND table_b.type in ( 1,2 )
       AND ( table_b.action IS NULL 
          OR table_b.action > NOW() )
     WHERE table_a.status > 100 
       AND table_a.status < 10000
       AND table_b.number IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT table_a.number 
      FROM table_a 
    LEFT OUTER
      JOIN table_b
        ON table_b.number = table_a.number  
       AND table_b.type in ( 1,2 )
       AND ( table_b.action IS NULL 
          OR table_b.action > NOW() )
     WHERE table_a.status > 100 
       AND table_a.status < 10000
       AND table_b.number IS NULL
    thnx for the suggestion, will give it a try
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    When I try the explain , I notice the following:
    ID select_type table type rows extra
    1 SIMPLE table_A ALL 79671 Using where
    1 SIMPLE table_B ALL 1011889 Using where


    My select is:
    SELECT *
    FROM table_a
    LEFT OUTER
    JOIN table_b
    ON table_b.nummer = table_a.dnummer
    AND table_b.ttype in ( 1,801 )
    AND ( table_b.tvolgendeactietijd IS NULL
    OR table_b.tvolgendeactietijd > NOW() )
    WHERE table_a.dstatus > 100
    AND table_a.dstatus < 10000
    AND table_b.nummer IS NULL

    I presume I have to create some indices in order to let it go faster?


    stefan

IMN logo majestic logo threadwatch logo seochat tools logo