August 7, 2013 at 9:50 am
I ran this on 2008 R2 and it ran without error and returned no rows.
August 7, 2013 at 9:55 am
Thanks, mleehayes. It must be me, I get same error on 2005 as well as 2008. hmmm....
August 7, 2013 at 10:49 am
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).
August 7, 2013 at 10:58 am
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...
August 7, 2013 at 12:44 pm
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) 🙂
August 7, 2013 at 12:50 pm
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]
August 7, 2013 at 12:50 pm
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.
August 7, 2013 at 12:53 pm
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...)
August 7, 2013 at 1:02 pm
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';
August 7, 2013 at 1:10 pm
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)
August 7, 2013 at 1:21 pm
Nice question and discussion. Thanks twin.
August 8, 2013 at 6:46 am
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)
August 8, 2013 at 6:55 am
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.
August 8, 2013 at 7:15 am
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
August 8, 2013 at 9:39 am
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 44 total)
You must be logged in to reply to this topic. Login to reply