Left outer Query optimization

  • hi

    following is the query to find data present in one column and not in other table

    select distinct([Baseball65K24].[Col005]) from  [Baseball65K24] left outer join  [Baseball65K50] on [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] where [Baseball65K50].[Col007] is null  order by[Baseball65K24].[Col005]

    is there a better way(performance wise) to implement it

     

    thanks

    Amrita

  • Can you post the full query. Posted query and the following would give the same result set.

    SELECT DISTINCT [Baseball65K24].[Col005])

    FROM

     [Baseball65K24]

    LEFT OUTER JOIN

     [Baseball65K50]

    ON

     [Baseball65K50].[Col007] =  [Baseball65K24].[Col005]

    WHERE

     [Baseball65K50].[Col007] IS NULL

    ORDER BY

     [Baseball65K24].[Col005]

    Also what is the purpose of [Baseball65K50].[Col007] IS NULL. This will make the comparison [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] impossible the join.

    Regards,
    gova

  • Can you post your execution plan for your query


    Kindest Regards,

    Vasc

  • LEFT OUTER JOIN

     will make that join possible and will return those rows that are in first table and not in second table  [Baseball65K50].[Col007] IS NULL


    Kindest Regards,

    Vasc

  • Make sure you have indexes on : [Baseball65K50].[Col007]  and  [Baseball65K24].[Col005]

    By the way I hope you are just testing and that those are not final column names!!!

     


    * Noel

  • Vasc What I mean was that does't mean any thing. Synatax is correct and the join will not add any thing. It is as good as not having that join.

    Regards,
    gova

  • govinn,

    Vasc is correct!!

    When you perform a left outer join and  the table column where the join can't  the two tables you get a null  if you then specify the IS NULL condition on the where clause you are effectively retrieving the rows that do not exist on the right side table!!!

     


    * Noel

  • Yes yes yes

    I have been doing this for zillion times. I need a coffee. Sorry for the confusion.

    My mind was thinking like below even after Vasc mentioned

     [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] AND

     [Baseball65K50].[Col007] IS NULL

    Regards,
    gova

  • Don't worry I have had my Lack of caffeine more than once too as a matter of fact I should be get a cup now!!

     


    * Noel

  • thnx for the reply

    we are using the query to find data from lefttable thats not available on right side table.

    is there a better way of performing it

    amrita

  • how if we put:

    select col005 from [Baseball65K24] a where not exists( select 1 from [Baseball65K50] b where a.col005 = b.col007)

    would it run faster?

    thanks.

    Leo

  • thnx a lot

    that was really fast

    jus one thing , can somebody explain me the diff between 2 queries , mine was a join n this is a subquery so shuldnt join be faster but in this case subquery is faster

    Amrita

  • Exists is not a subquery, it only returns true or false, so no data ia actually fecthed from the clustered index (besides the minimum it takes to make sure you get a hit).

  • Just want to add that when the number of repeated keys on the inner table is large exists is always a superior choice but when the keys are not repeated too much you could get better performace from the Join

    Therefore this will always depend on the data and you should try both cases to determine what's  best!

     


    * Noel

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply