Bob Cullen-434885 (2/24/2014)
OK. I got it wrong, and now I know why I got it wrong. What I cannot understand is why it behaves like that in the first place! Surely if I say select 'this' from 'that', and 'that' does not have a 'this' it should generate an error.
Has anybody got a good explanation for why it does what it does, and where that would make sense in everyday use? Please?
I've changed the DDL slightly to illustrate the point: -
IF object_id('tempdb..#category') IS NOT NULL
DROP TABLE #category;
IF object_id('tempdb..#product') IS NOT NULL
DROP TABLE #product;
SELECT ID, CategoryName
FROM (VALUES(1,'Cutlary'),(2,'Glassware'))a(ID, CategoryName);
SELECT ID, ProductName, CategoryID
FROM (VALUES(1,'Knife',1),(2,'Fork',1),(3,'Spoon',1),(4,'Tumbler',2),(5,'Pint Glass',3))a(ID, ProductName, CategoryID);
Now, imagine that instead of the query that the QOTD is showing, we were doing a JOIN.
SELECT CategoryName, ProductName
FROM #category cat
INNER JOIN #product prod ON cat.ID = prod.CategoryID;
You'd expect SQL Server to know where each of those columns in the SELECT statement come from, despite the lack of alias', due to logical query processing order, correct? Why would you think a sub-query in a WHERE clause would act any differently?
It's simply one of the many reasons why good alias' should be used whenever possible.