Left join vs Exists

Question

Left join vs Exists

mihir.nasikkar 40164

SSC Rookie

Points: 28
More actions
October 1, 2015 at 5:40 am

#305116

SELECT DISTINCT mv.meetID,
mv.FundID,
CASE WHEN vcv.vote IS NULL
THEN mv.VoteFlag
ELSE (CASE WHEN vcv.vote IN ('Unknown','Refer',' ','Did Not Vote','None') Then 0 Else 1 END) END AS VoteFlag
FROM dbo.Information mv
LEFT OUTER JOIN dbo.InformationOverride vcv ON vcv.meetID = mv.meetID AND vcv.FundID = mv.FundID AND vcv.agendaID = mv.agendaID
Can this query be written using exists clause ?
The information table has close to 4.5 million records . MeetID has clustered index and there non clustered on fundID.
There is composite clustered index on InformationOverride table on MeetID, FundID and AgendaId.

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mihir.nasikkar 40164 (10/1/2015)
SELECT DISTINCT mv.meetID, mv.FundID, CASE WHEN vcv.vote IS NULL THEN mv.VoteFlag ELSE (CASE WHEN vcv.vote IN ('<Unknown>','Refer','','Did Not Vote','None') Then 0 Else 1 END) END AS VoteFlag FROM dbo.Information mv LEFT OUTER JOIN dbo.InformationOverride vcv ON vcv.meetID = mv.meetID AND vcv.FundID = mv.FundID AND vcv.agendaID = mv.agendaID

What is your question?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

No, you can't use EXISTS to return data.

As to performance, the non-clustered index on the vcv (InformationOverride) table would have to INCLUDE the vote column to be useful.

Better would very likely be to cluster the vcv table first on meetID; you may want/need to add FundID and agendaID, can't tell from this query alone.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 3

Not sure what you are looking for...if just want to get rid of left outer join, a sub-query for table InformationOverride would work. If record does not exist, null is returned.

myzus2004 Old Hand Points: 378 More actions · Answer 4

Thanks scott for your feedback. Appreciate it .

The clustered index on InformationOverride table cannnot be changed as the composite index on these 3 columns have unique data.

I am trying to optimize this specific code block hence trying out if i can write using exists clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The difference between EXISTS and JOIN is minimal, so even if you could, there wouldn't be much point in doing so.

Also, a clustered index doesn't have to be unique, it's just a strong guideline that it is.

What nonclustered indexes exist on the tables? Can you post an execution plan?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

myzus2004 (10/3/2015)
Thanks scott for your feedback. Appreciate it .
The clustered index on InformationOverride table cannnot be changed as the composite index on these 3 columns have unique data.
I am trying to optimize this specific code block hence trying out if i can write using exists clause.

Ah, you've changed your original q to now indicate that the vcv table is clustered by meetID, where as it originally said something different.

In that case, yes, my advice is mute, since it's already been done :-D.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

It seems to me that, if InformationOverride is meant for exclusion, then you're missing a "WHERE vcf.MeetID IS NULL" clause, or if InformationOverride is meant for inclusion, then it should be an INNER JOIN instead of a LEFT (OUTER) JOIN.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho