SubQuery

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Comments posted to this topic are about the item SubQuery

  • Lokesh Vij

    SSChampion

    Points: 10836

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • sqlnaive

    SSCoach

    Points: 17435

    Good question. Read all the options and with each option my answer changed and finally got the right one.

  • kalyani.k478

    Default port

    Points: 1400

    Easy one for Friday:-)

  • Dineshbabu

    Hall of Fame

    Points: 3220

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

  • Stuart Davies

    SSCoach

    Points: 18874

    Thanks for the question - easy way to end the week

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere 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

  • arun1_m1

    SSCommitted

    Points: 1949

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

  • archimed7592

    Valued Member

    Points: 52

    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.

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Mostly i will go for JOins. I have practiced right from the begining like that.. I hate subqueries.

    --
    Dineshbabu
    Desire to learn new things..

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 841

    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

  • Toreador

    SSChampion

    Points: 11239

    Hugo Kornelis (1/11/2013)


    NOT IN can only be used in limited cases. The comparison condition must be multi-column

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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Toreador (1/11/2013)


    Hugo Kornelis (1/11/2013)


    NOT IN can only be used in limited cases. The comparison condition must be multi-column

    Do 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 27 total)

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