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