August 8, 2013 at 10:17 am
Hugo, Conventions & parsing aside. Are we to believe that someone would write something so convoluted to demonstrate Exists/Not Exists Syntax ( select(1=1))
I mean it doesn't even make sense to join across keys that have no association.
August 8, 2013 at 10:34 am
Stevenna1 (8/8/2013)
Hugo, Conventions & parsing aside. Are we to believe that someone would write something so convoluted to demonstrate Exists/Not Exists Syntax ( select(1=1))I mean it doesn't even make sense to join across keys that have no association.
First (defending the author of this question) - for a QotD, realism is not really an issue. I've seen questions much more convoluted than this,
Second, I think this question is valuable because it demonstrates the risk of using columns in a subquery without prefixing them with the table or alias. If you don't a simple misspelling, or misremembering the name of a column, might result in very hard to find bugs - if the misspelled or misremembered column name happens to match a column in the outer table.
For this question, the setup may be convoluted. But errors like that do happen in the real world. And they can cause people to bang their heads against the wall for a long time, and then slap their forehead when they finally see the cause.
August 8, 2013 at 11:40 am
Ok Hugo, My last point, because the Qotd, I agree, its nice to see challenging things.
But the answer; Simply changing the SQL to Exists makes the whole thing right ?
I'll politely disagree. Unfortunately coding like this. Whether its qualified properly or not, just simply isn't a valid or useful example.
And the author doesn't explain anything properly, and I certainly hope no-one would use a join situation like this to control a set.
People are learning from these examples, taking them as reference and applying them to projects where people pay them real money 🙂
I'll go one further, anyone that wrote code like this for me, honestly; would be shown the door.
August 8, 2013 at 12:52 pm
Stevenna1 (8/8/2013)
Ok Hugo, My last point, because the Qotd, I agree, its nice to see challenging things.But the answer; Simply changing the SQL to Exists makes the whole thing right ?
I'll politely disagree.
If you're talking about changing between IN and EXISTS - indeed, that would not solve anything. I thought that was already mentioned in the discussion? (If not, my memory is playing tricks on me).
If you're talking about something else, I don't get the point. Care to expand?
Unfortunately coding like this. Whether its qualified properly or not, just simply isn't a valid or useful example.
If all columns had been qualified, I would see nothing wrong with the example. Assuming, that is, that the IN was indeed intended to be a correlated subquery.
SELECT c.ID, c.NAME
FROM @customer AS c
WHERE NOT EXISTS
(SELECT *
FROM @Order AS o
WHERE o.ID = c.ID);
I see nothing wrong with the above query - except, of course, that the o.ID column doesn't exist. The writer of the query probably misremembered the column names. A very human (and common, if I can judge by personal experience) mistake. And because of the column qualification, you'll get a nice, clean error message, know what you did wrong, and you can correct it.
And the author doesn't explain anything properly
True, the explanation of this question could have been better. That has already been mentioned in the discussion. (And that's why it always pays off to check the discussion after replying to a QotD, there's often a lot of useful additional information there).
and I certainly hope no-one would use a join situation like this to control a set.
People are learning from these examples, taking them as reference and applying them to projects where people pay them real money 🙂
Based on this quote, I get the feeling that you see more errors in this code than I have seen and commented on so far. Maybe you can point out what (other) problems in the query are so bad?
I'll go one further, anyone that wrote code like this for me, honestly; would be shown the door.
No matter how bad the code I get from a new hire, they're never shown the door for it. Instead, I'd show them an explanation. The best source of learning is mistakes; seniors should not punish juniors on those mistakes; they should use the learning opportunity.
(Now, if a hire keeps repeating the mistakes and obviously doesn't want to learn ... that's another story)
August 8, 2013 at 1:22 pm
No matter how bad the code I get from a new hire, they're never shown the door for it. Instead, I'd show them an explanation. The best source of learning is mistakes; seniors should not punish juniors on those mistakes; they should use the learning opportunity.
I was not talking about "Juniors". But I get your point. (Just for reference, We don't hire juniors in this particular shop). Most of the business is in high risk areas.
And as for mistakes, quite clearly, the only way to learn is making mistakes. Quite literally nothing in this world that comes easy is worth having.
Keep in mind this is a published article/Qotd. I'd say accuracy counts for something.
My comments , although they have been addressed to you, are more for the authors consumption and are hopefully constructive.
If you have a venue where you are getting published ( or even better, a job where you are paid)
For a venue like the Qotd ; concise examples with clear explanations is fundamental.
If the code is intentional & convoluted to prove a point in the parsing or query evaluation, or how the optimizer works, that's great.
Trick question, even better.
But in this case, the solution to fix the "issue" was this (sic).
If we share the where clause from NOT EXISTS to EXISTS it will show all the records. for more detail please refer to the link below.
I think we are supposed to learn from the questions of the day 🙂 not mentor the author. (and yes, we can all learn from anyone anytime; believe me I get it)
August 8, 2013 at 1:42 pm
Stevenna1 (8/8/2013)
But in this case, the solution to fix the "issue" was this (sic).If we share the where clause from NOT EXISTS to EXISTS it will show all the records. for more detail please refer to the link below.
I think we are supposed to learn from the questions of the day 🙂 not mentor the author. (and yes, we can all learn from anyone anytime; believe me I get it)
Ah, now I get it. I had interpreted that part of the explanation as another tidbit of information (or, to put it differently, as another interesting side effect of the actual problem in the subquery), not as a solution to the fix.
But I see how people could interpret it differently. Again, complete agreement that the explanation is weak.
August 9, 2013 at 9:20 am
Bottom line:
1. Be consistent in aliasing, and develop a style/standards guide.
2. Not exists is very useful in:
A. Proving the "many" side of a one-to-many relationship does not exist without introducing the risk of returning duplicate rows.
B. (During my experience) usually being more performant than "not in".
I WILL say NOT EXISTS may not be very intuitive at first in comparison to a relational join, and (in my experience) NOT EXISTS is one of the most underutilized weapons in a query arsenal.
August 10, 2013 at 12:17 pm
Hugo Kornelis (8/8/2013)
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:
SELECTID, 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.
Thanks Hugo.. Nice explanation..
August 12, 2013 at 4:26 am
Thanks Hugo for taking so much from the other for me ... really appreciated !!!
For the explanation weak thing ... i accept the explanation is not as detailed as it should be ...
but it enough to make the reader say that "How it 1=1" instead of saying "Why 1=1"
secondly, used exists because usually people use exists too much and have tendency to use alias like no other ...
For those who does these kind of things in writing sql they should be know what kind of issue could popup if they not follow the proper coding standard ...
Beside that thanks all for the comments ... 🙂
August 12, 2013 at 7:05 am
twin.devil (8/7/2013)
Thanks for the comments guys....Actually this was a real time issue, when people just write queries in a hurry and forget to set the proper alias. 😎
This would have been a better explanation of your question than 1=1.
😉
August 13, 2013 at 5:07 am
I got this wrong because I thought the author had made a mistake in his code, i.e. he meant to write WHERE customerID... but wrote WHERE ID... . I didn't consider that he was trying to show that SQLServer would look outside the NOT EXISTS brackets for the column ID when it sees that column ID doesn't exist on table @Order.
Seeing as we don't employ trainees, I'd be disappointed in anyone who wrote code like this without the appropriate table.column usage.
August 13, 2013 at 6:12 am
Really nice question.
August 16, 2013 at 1:25 am
Really good question. And equally good explanations by kingston and Hugo. Cheers. 🙂
Seriously no fuss about the extra ordinary out of the box challenges to the question here ???? 🙂 (Like
SELECT
ID, NAME
FROM @customer C
WHERE NOT EXISTS ( SELECT 1
FROM @Order WHERE ID = c.ID)
case sensitive issue with c.ID to alias C ??? 😀
)
August 16, 2013 at 2:00 am
Thanks for the comments 🙂
i like the case sensitive thing 😛
August 19, 2013 at 6:10 am
That's really a great explanation by Kingston and in fact the question can be posted in multiple ways where the following is one such type
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 OrderID = c.ID)
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply