I guess the subtle point is that SQL Server will NOT throw any syntax errors because ultimately what the user is making is a logical error.

It's one of those things that one would miss when writing code in a hurry, and then spend endless nights trying to figure out what went wrong.