Problem with NOT EXISTS
-
Hello,
I am trying to discover the presence or absence of records between two tables using NOT EXISTS. The query I am using is as follows:
SELECT
Permnum as TSTPermnum,
TestShortName as TSTTestshortName,
DateEntered AS TSTDateEntered
FROM #tblMMStudentTestScoresTEMP
WHERE NOT EXISTS
(SELECT TS.Permnum, TS.TestShortName, TS.DateEntered
FROM tblMMStudentTestScores TS)
In order to test this, I removed the records associated with Permnum 600 from tblMMStudentTestScores, and left those same records in the #tblMMStudentTestScoresTEMP table. I expected that the above query would return the records associated with Permnum 600, but instead, no records where returned.
If I try the query without either 'DateEntered' field, I have the same problem.
Why does this query not do what I anticipate, and what would I need to do to get the results I expect?
Thanks for your help!
CSDunn
-
The where not exists should be changed ...
- SELECT ... from #tblMMStudentTestScoresTEMP abc
- WHERE NOT EXISTS
- (SELECT 'y'
- FROM tblMMStudentTestScores
- where Permnum = abc.Permnum
- and ShortName = abc.ShortName ... etc
-
Thanks! As it turns out, I had tried setting the fields equal, but was using a 'DateEntered' field that was set up as a default of GETDATE() on the table I was inserting to, so the records between the two tables never matched.
I don't know if that makes sense, but thanks anyway.
CSDunn
-
You might notice better performance if you use an outer join instead of the not exists. IE:
SELECT ...
FROM tblMMStudentTestScores TS
LEFT JOIN #tblMMStudentTestScoresTEMP T ON ...
WHERE T.?? IS NULL
If you have a lot of records to process the outer join will often be the better performer. Expecially if you add indexes to the temp table.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I guess that should be a right join because the exists-function is done using the tblMMStudentTestScores.
But because I prefer using Left joins (I find it smoothly to read) ...
SELECT ...
FROM #tblMMStudentTestScoresTEMP T
LEFT JOIN tblMMStudentTestScores TS ON ...
WHERE TS.?? IS NULL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I might be wrong, but IMHO in almost any case using (NOT) EXISTS will be faster to check for existence, because EXISTS can stop once the logical test proves true.
But that's just me
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If the relation between the temp-tb and the tb is 1-1 and you're checking existance on the unique key, supporting it with a clustered index might speed up this join-operation.
With the outer-join solution it will have to execute the full join once, with the exist-solution it will have to perform the correlated query per row in a "top 1"-fasion.
As always, you'll have to compare execution plans and runtimes to build your choice on.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes!
There's always more than one way to skin the cat (Hey, did anybody notice I learned a new phrase
)--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hm... with the help from Inside Sql Server 2000 especially the chapter on query optimization and join processing I still think that using NOT EXISTS is almost always faster than using an OUTER JOIN.
...but I might be wrong. After all I'm not really in the IT business at all
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hey, I thought the were bits and pieces of bricks and cement with your last reply
As far as I'm concerned you may as well be a shoe-salesman (Al B.) with his wireless cash register, who found out he can do more then registering prices.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- I'm a little bit slow in understanding today. Can you explain your last reply please?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree with Frank. If a query can be written using NOT EXISTS rather than an outer join, that's usually the way to go for best performance. Even if the optimizer doesn't use TOP, it will typically choose an Anti Semi Join, which is twice as fast as an Outer Join.
If I can use NOT EXISTS, I don't even test against outer joins anymore, as I never found a case where the explicit join was faster. I'd love to see an example where it is, though, as then I'll resume testing both ways.
--Jonathan -
As always, you'll have to compare execution plans and runtimes to build your choice on. Hey, this may even by the occasion for a 'aha-erlebnis'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
????
Hey alzdba, am I asking for too much when I want to have an explanation for something I don't understand?
Right now I'm insecure if I should feel offended or not
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Does this mean you're going to post an example where the outer join is faster than NOT EXISTS?
--Jonathan
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply