Anti-join on single table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Anti-join on single table

  1. #1
    Join Date
    Aug 2007
    Posts
    62

    Anti-join on single table

    Hi,

    I need to optimize a SQL query which does an anti-join on one table.

    select A from table_t
    where A not in
    (select distinct A from table_t
    where B in ('ABC','DEF') and C ='LS')

    I read about using NOT EXISTS in place of NOT IN but i can't seem to implement it into my query as the examples shown uses anti-join on 2 tables.

    Is there a way to optimize an anti-join on one table? Will appreciate it if you can show me a link or so.

    Thanks a lot!

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    why do u need to use the table twice?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2007
    Posts
    62
    I get you point!

    You see, i was trying very hard to avoid using NOT IN/NOT EXISTS but it turns out that the situation is worse now that i scan the table twice.

    This is the original query:

    select A from table_t
    where B not in ('ABC','DEF') and C ='LS'

    I guess i just have to live with it.

    Thanks abhaysk!

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    if this the original query.. then the query in ur first post not only uses table twice but also logically not in sync with this query..

    live with what you have..
    Last edited by abhaysk; 09-28-2007 at 09:15 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Aug 2007
    Posts
    62
    Hi abhaysk,

    Do you mean that
    select A from table_t
    where B not in ('ABC','DEF') and C ='LS'

    is different from
    select A from table_t
    where A not in
    (select distinct A from table_t
    where B in ('ABC','DEF') and C ='LS')
    ?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Yes..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Aug 2007
    Posts
    62
    I get what you mean ...

    Created a table to see the results.

    Supposed i have a table table_t:

    ID OCDATE TC

    1 9/26/2006 CB
    1 9/26/2006 UT
    2 9/26/2006 CS
    2 9/26/2006 UT
    3 9/26/2006 CS
    4 9/26/2006 CB
    5 9/26/2006 CS
    4 9/26/2006 SS
    5 9/26/2006 SS
    6 9/26/2006 SS
    7 9/26/2006 SS

    Select id from table_t where
    select distinct id from table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and tc not in ('CB', 'CS', 'UT')

    ID

    6
    7
    5
    4

    select id
    from
    table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')

    ID

    1
    1
    2
    2
    3
    4
    5
    4
    5
    6
    7

    select id
    from
    table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and id not in (
    select distinct id from table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and tc not in ('CB', 'CS', 'UT')
    )

    ID

    1
    1
    2
    2
    3

    Is there a way to avoid 2 table scans on table_t for this query?
    select id
    from
    table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and id not in (
    select distinct id from table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and tc not in ('CB', 'CS', 'UT')
    )

    Thanks abhaysk for pointing out my mistake!

  8. #8
    Join Date
    Aug 2007
    Posts
    62
    WOAH!!!

    This is very fast!
    Thanks a million!!!

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Get me the exact requirement (my earlier post has one logical error & i have deleted that one).
    Last edited by abhaysk; 10-01-2007 at 06:29 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Aug 2007
    Posts
    62
    Hi abhaysk,

    just found out that your previous post doesn't give the correct results.

    Anyway, here's my requirements.

    Supposed i have a table table_t:

    ID OCDATE TC

    1 9/26/2006 CB
    1 9/26/2006 UT
    2 9/26/2006 CS
    2 9/26/2006 UT
    3 9/26/2006 CS
    4 9/26/2006 CB
    5 9/26/2006 CS
    4 9/26/2006 SS
    5 9/26/2006 SS
    6 9/26/2006 SS
    7 9/26/2006 SS

    Output should be:

    ID

    1
    1
    2
    2
    3

    My original query:
    select id
    from
    table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and id not in (
    select distinct id from table_t
    where
    ocdate >= to_date('9/26/2006','mm/dd/yyyy')
    and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
    and tc not in ('CB', 'CS', 'UT')
    )

    Thanks alot for your time and effort!

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