Exists

  • I ran this on 2008 R2 and it ran without error and returned no rows.

  • Thanks, mleehayes. It must be me, I get same error on 2005 as well as 2008. hmmm....

  • Michael_Garrison (8/7/2013)


    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.

    That's not related to the version, but to the collation. Your server uses a case sensitive collation, and the question author apparently only tested on a server with a case insensitive collation. So he missed the difference between c and C (lower- and uppercase).


    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/

  • Erueka, thanks Hugo Kornelis. I had not even thought of Collation. Makes sense now. Learning something new everyday is a good thing. Ok, I learned my lesson, get plenty of coffee first before trying to answer the questions here. And make sure I try and understand what the intent is.... thanks everyone...

  • Run it against 2005...I bet you'll get a syntax error. Agree with answer for 2008+ but not when run against 2005 (still supporting the older version) πŸ™‚

  • This was a good question, and I am sad to say that I missed it. I thought there would be an error thrown since row 16's ID was not written as c.ID.

    I decided to run this on my system, and I did receive an error (see attachment). Since this will not be bothering us much longer I am curious what part of the query will not work in SQL 2K? Was there an upgrade moving to 08 R2 that allowed comma delimited Values statements?

    [font="Arial"]β€œAny fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • Hugo Kornelis (8/7/2013)


    Michael_Garrison (8/7/2013)


    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.

    That's not related to the version, but to the collation. Your server uses a case sensitive collation, and the question author apparently only tested on a server with a case insensitive collation. So he missed the difference between c and C (lower- and uppercase).

    Thanks, Hugo. I was wondering why such a large plurality didn't answer the QotD correctly -- I had noticed the case difference between the specification of the table alias and its use in the subquery, but I didn't know it could be significant.

    +1 in re to your two "I always do this" thoughts -- I've learned the hard way that it's best to alias every table specified in the FROM clause and to use those aliases in the SELECT -- and if I may put in my tuppence re MTassin's question to you, I generally use commas at the end of a line (I find that more legible) but sometimes switch to using commas at the beginning of a line while developing the code, as such a practice makes it easier to comment a given line out.

  • mtassin (8/7/2013)


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

    Sorry, missed that question earlier.

    I try to get myself to put the comma's at the start, because that makes it much easier to comment out a few columns. regardless of their position in the SELECT list.

    But in reality, I almost always put them at the end. That just feels more natural (after all, I have first learned to use comma's in written language, and they're directly after a word there...)


    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/

  • jarid.lawson (8/7/2013)


    I decided to run this on my system, and I did receive an error (see attachment). Since this will not be bothering us much longer I am curious what part of the query will not work in SQL 2K? Was there an upgrade moving to 08 R2 that allowed comma delimited Values statements?

    SQL Server 2008 (not R2) introduced the option to insert multiple rows with INSERT VALUES.

    So "Insert into @customer Values (1,'JOHNNY BRAVO'), (2,'MICKI'), (3,'POWERPUFF GIRLS')" works on SQL2008 and up; on SQL2005 and below, you'd have to use either of the below:

    INSERT INTO @customer

    VALUES (1,'JOHNNY BRAVO');

    INSERT INTO @customer

    VALUES (2,'MICKI');

    INSERT INTO @customer

    VALUES (3,'POWERPUFF GIRLS');

    INSERT INTO @customer

    SELECT 1,'JOHNNY BRAVO'

    UNION ALL

    SELECT 2,'MICKI'

    UNION ALL

    SELECT 3,'POWERPUFF GIRLS';


    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/

  • Totally agree with Hugo Kornelis, hence my answer that the server returns an error and my reply to this thread regarding that it is a syntax error - Hugo, great job explaining and clarifying that it was in 2008+ (not R2)

  • Nice question and discussion. Thanks twin.

  • Am I missing something here ?

    The query has a BUG in it it has nothing to do with 1=1

    Declare @customer Table ( ID int

    , Name varchar(20)

    );

    Declare @Order Table ( OrderID int identity(1,1)

    , customerID int

    );

    Insert into @customer

    Values (1,'JOHNNY BRAVO'), (2,'MICKI'), (3,'POWERPUFF GIRLS')

    INSERT INTO @Order

    VALUES (1), (3)

    SELECT

    ID, NAME

    FROM @customer C

    WHERE NOT EXISTS ( SELECT 1

    FROM @Order WHERE customerID = c.ID)

  • Stevenna1 (8/8/2013)


    Am I missing something here ?

    The query has a BUG in it it has nothing to do with 1=1

    Declare @customer Table ( ID int

    , Name varchar(20)

    );

    Declare @Order Table ( OrderID int identity(1,1)

    , customerID int

    );

    Insert into @customer

    Values (1,'JOHNNY BRAVO'), (2,'MICKI'), (3,'POWERPUFF GIRLS')

    INSERT INTO @Order

    VALUES (1), (3)

    SELECT

    ID, NAME

    FROM @customer C

    WHERE NOT EXISTS ( SELECT 1

    FROM @Order WHERE customerID = c.ID)

    You call it a bug, because you (think you) know what the query was intended to be (and I'd guess you're probably right).

    But SQL Server simply follows the rules.

    For column names that are not qualified with a table name or alias, the rule is to first try to find a match in the current scope; if that fails and the current scope is a subquery, SQL Server will then try the outer scope. (And so on, in the case of nested subqueries).

    So for the unqualified ID in the subquery, SQL Server first tries to find it in the subquery - i.e., in @Order. There is no column with that name in that table, so SQL Server moves on to the outer query level - and there, a matching column is found! (the ID column in the @customer table). So SQL Server interprets this as if the query was written like this:

    SELECT

    ID, NAME

    FROM @customer C

    WHERE NOT EXISTS ( SELECT 1

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

    The check on C.ID = C.ID is of course true for every row in the @Order table, so the NOT EXISTS returns false (as there does exist a row that qualifies the conditions in the subquery) for every row in @customer.


    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/

  • Nice question, but the "Exists" title is utterly misleading, the question is really about name resolution for unqualified column names in subqueries.

    Hugo's explanation is great and his aliasing practise is usually good; but sometimes it is better to write the full table name as a qualifier rather than use an alias - because sometimes it is important to emphasize exactly what comes from where in order to make the code easier for people to understand.

    Tom

  • Good question. I almost answered under the assumption the Order table had an ID column, but realized it was named differently in time.

Viewing 15 posts - 16 through 30 (of 45 total)

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