SubQuery
-
Comments posted to this topic are about the item SubQuery
-
Easy and nice question to end the week. Thanks!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Good question. Read all the options and with each option my answer changed and finally got the right one.
-
Easy one for Friday:-)
-
Nice and easy one.. AS well as blog by pinal dave which compares performance between NOT IN and JOINS also very useful..
--
Dineshbabu
Desire to learn new things.. -
Thanks for the question - easy way to end the week
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Easy one.. But when I think I do find OUTER JOINS to work outfaster than NOT IN, of course it depends, but I will bet on JOINS as compared to NOT IN.
Have a great weekend.. 🙂
-
You might elaborate on the condition which is used to determine existence of the record.
For example, if you have multicolumn key than you can't use NOT IN
For single-column key all three choices are correct.
-
Mostly i will go for JOins. I have practiced right from the begining like that.. I hate subqueries.
--
Dineshbabu
Desire to learn new things.. -
I don't really agree with the answer marked as correct.
NOT IN can only be used in limited cases. The comparison condition must be single-column, and there must not be any null values in the comparison column in the subquery. Because of those limitations, I never use NOT IN for this kind of questions - I prefer to use the same method in all cases.
NOT EXISTS and the outer join with IS NULL test can both be used under all circumstances, but I far prefer the NOT EXISTS method, because it is far easier to write, and (even more important!) to understand when you have to revisit your code later. In most cases, the execution plan will be the same, so there is no performance benefit of one over the other (this was not always true in older versions of SQL Server, but that has long been changed).
So the technically correct answer to the question would be that only NOT EXISTS and outer join with IS NULL test (a so called anti-semi-join) can always be used. And I would add that I recommend sticking to the NOT EXISTS method for maintainability, unless you happen to run into a situation where the execution plans are not equal, and the plan with the anti-semi-join performs better - and even in that case, I would still prefer the NOT EXISTS version, unless the performance difference prevents me from achieving the required response times.
P.S. The article by Pinal Dave that is referenced only compares anti-semi-join with NOT IN, not with NOT EXISTS; they are not always equivalent (when NULL values can be involved). And he then bases his conclusions on the percentages in the execution plan output; these are estimated costs, not actual costs, and they can be very wrong - so this is one of the worst methods for determining if a query is faster or not.
EDIT: Corrected an embarassing typo
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hi,
Where is EXCEPT?
It is very practical.
See this: http://msdn.microsoft.com/en-us/library/ms188055.aspx
Thanks,
IgorMi
Igor Micev,My blog: www.igormicev.com
-
Hugo Kornelis (1/11/2013)
I don't really agree with the answer marked as correct.<snip the arguments for brevity>
+1
I have gotten used to the OUTER JOIN version myself, and for much the same reasons.
-
This was removed by the editor as SPAM
-
Hugo Kornelis (1/11/2013)
NOT IN can only be used in limited cases. The comparison condition must be multi-columnDo you mean 'the comparison condition cannot be multi-column'?
That's one of the ways where Oracle scores over SQLServer, you can have multi-column IN statements.
I normally use either NOT EXISTS or EXCEPT.
-
Toreador (1/11/2013)
Hugo Kornelis (1/11/2013)
NOT IN can only be used in limited cases. The comparison condition must be multi-columnDo you mean 'the comparison condition cannot be multi-column'?
Oops, that is of course exactly what I meant. I edited my post to correct this stupid mistake.
That's one of the ways where Oracle scores over SQLServer, you can have multi-column IN statements.
Agreed. That feature is called "row value constructors", and it's actually part of the ANSI standard - but not implemented in SQL Server, even though it would be incredibly useful for lots of situations. I actually have a suggestion on Connect to add this feature - feel free to add your vote if you agree. Link: https://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors
That being said, for checking for the absence of a row in another table, I would still prefer NOT EXISTS over NOT IN, because of the issue with NULL values.
I normally use either NOT EXISTS or EXCEPT.
EXCEPT has been mentioned by several people. This is a great feature if the tables have the same columns, or at least if you can select the same columns from both tables. So if you have a Customers table and a LoyalCustomers table with the same columns, you can use the EXCEPT operator to find the "less loyal" customers. But if columns lists are different, EXCEPT won't work - e.g. you can not easily use EXCEPT to produce a list of customer details for customers who don't exist in the OrdersFromLastTwoYears view.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply