Exists

  • twin.devil

    SSC-Insane

    Points: 22208

    Comments posted to this topic are about the item Exists

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good question....

    Thanks twin 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Most of time i try to avoid Exists clause. I will prefer join or sub query.

    Good question 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!
    www.GrowWithSql.com

  • Kingston Dhasian

    SSCoach

    Points: 19792

    Good question, but the explanation could have been better.

    You could have explained why the condition is similar to "WHERE 1 = 1"

    Your query is as below

    SELECT

    ID, NAME

    FROM @customer C

    WHERE NOT EXISTS ( SELECT 1

    FROM @Order WHERE ID = c.ID)

    As you haven't specified the alias name for the left column in you WHERE clause in EXISTS checking, SQL Server checks if ID exists in the @Order table

    Since, ID doesn't exist in the @Order table, it will now check in the outer table which is @customer

    Your query then effectively becomes the below

    SELECT

    ID, NAME

    FROM @customer C

    WHERE NOT EXISTS ( SELECT 1

    FROM @Order WHERE c.ID = c.ID)

    c.ID will always be equal to itself unless it is NULL and hence the behavior similar to "WHERE 1 = 1"


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • twin.devil

    SSC-Insane

    Points: 22208

    Thanks for the comments guys.

    @Kingston Dhasian: your explanation is the correct one.

    Actually this was a real time issue, when people just write queries in a hurry and forget to set the proper alias. 😎

    Actually it was a real time issue happens with my colleague, he asked to check what is wrong with the query and when i told him the issue he was like :w00t:, He kept saying that SQL SERVER should return a error in this case 😀 ....

  • Neeraj Prasad Sharma

    Ten Centuries

    Points: 1285

    Danny Ocean (8/6/2013)


    Most of time i try to avoid Exists clause. I will prefer join or sub query.

    Good question 🙂

    sub query ? does that means in clause as well please look into below query

    SELECT

    ID, NAME

    FROM @customer C

    WHERE C.ID not in ( SELECT ID

    FROM @Order )

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Danny Ocean (8/6/2013)


    Most of time i try to avoid Exists clause. I will prefer join or sub query.

    Good question 🙂

    Exists IS (or rather, uses) a subquery. If you mean that you prefer to use a subquery with IN rather than EXISTS, you're opening yourself up for another surprise - NULL values don't behave as many people expect in a NOT IN. And you cant use [NOT] IN if you need a match on two or more columns.

    Joining avoids the subquery (but would produce the same unwanted results if you fail to qualify the columns with table names). Joining also has the problems that (a) if you use it to simulate a NOT IN, you use a bit of logic that many people don't immediately understand (the socalled "anti semi join") - a risk for future maintenance; and (b) if you use it to simulate an IN, you can get extra rows if the match criterium is non-unique in the joined table.

    My coding style is to:

    * Use joins only when I actually need columns from all joined tables;

    * Use EXISTS and NOT EXISTS for any check of existence in a different table (or view) source;

    * Use IN and NOT IN only with a list of constants, never with a subquery (because that can be done with IN).

    And to avoid the problem highlighted in this QotD, I religiously follow these two additional guidelines:

    * For any query involving two or more tables, I assign an alias to each table (not really required, but makes the next guideline easier); and

    * For any query involving two or more tables, I prefix every column with the proper table alias.

    This not only prevents unexpected results when misspelling a column name, it also makes my queries easier to understand and maintain for whoever inherits them when I leave.


    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/

  • This was removed by the editor as SPAM

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good explanation Kingston 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    Interesting question, but the explanation could be a bit better.

    Thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the question, and for contributing to the site.

    (And thanks to Hugo for the usually brilliant post that tidies things up a bit and then some.)

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • mtassin

    SSC-Insane

    Points: 23035

    Hugo Kornelis (8/7/2013)


    My coding style is to:

    * Use joins only when I actually need columns from all joined tables;

    * Use EXISTS and NOT EXISTS for any check of existence in a different table (or view) source;

    * Use IN and NOT IN only with a list of constants, never with a subquery (because that can be done with IN).

    And to avoid the problem highlighted in this QotD, I religiously follow these two additional guidelines:

    * For any query involving two or more tables, I assign an alias to each table (not really required, but makes the next guideline easier); and

    * For any query involving two or more tables, I prefix every column with the proper table alias.

    This not only prevents unexpected results when misspelling a column name, it also makes my queries easier to understand and maintain for whoever inherits them when I leave.

    +1 This... I think Hugo and I follow the same basic practices here...

    tell me Hugo, do you put commas at the end or beginning of a line?



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • roe3p

    Old Hand

    Points: 344

    Hi all, I'm new to this site, but is there the assumption that the question is only asked of a minimum version of SQL Server?

    SQL Server 2005 doesn't allow comma-separated inserts for example, so the code in question does throw an error unless it's changed to:

    Insert into @customer Values (1,'JOHNNY BRAVO')

    Insert into @customer Values (2,'MICKI')

    Insert into @customer Values (3,'POWERPUFF GIRLS')

    etc.

    I know I'm nitpicking considering it's an 8 year old product, but some of us still use it 😉

  • Michael_Garrison

    Hall of Fame

    Points: 3000

    Good question. I missed this because on SQL 2008 R2 I get error with 'c.ID could not be bound'. So I picked SQL would return an error. Obviously I missed the whole point of the question. But I have noticed a lot of times I get the wrong answer because of my older SQL version.

  • Michael_Garrison

    Hall of Fame

    Points: 3000

    Ah, posted my answer before I read roe3p post. Same idea I was trying to make.

Viewing 15 posts - 1 through 15 (of 45 total)

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