Left outer Query optimization

Question

Post reply

Left outer Query optimization

Amrita bhatia

Right there with Babe

Points: 718
More actions
August 17, 2005 at 7:45 am

#88761

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

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

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

Can you post your execution plan for your query

Kindest Regards,

Vasc

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 3

~~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

noeld SSC Guru Points: 96590 More actions · Answer 4

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

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

noeld SSC Guru Points: 96590 More actions · Answer 6

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

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

noeld SSC Guru Points: 96590 More actions · Answer 8

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

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 9

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

Leo Leong SSCommitted Points: 1615 More actions · Answer 10

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

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 11

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

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).

noeld SSC Guru Points: 96590 More actions · Answer 13

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