September 27, 2010 at 9:28 am
Chris Morris-439714 (9/27/2010)
krishnakumar-293737 (9/27/2010)
Select top 20 * from Database1..MY_TABLE (nolock)Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')
Order By MY_COL desc
Now....the subquery is invalid, there is no column THE_ID on Database2..MY_TABLE_2.
But when I run the above query, it gives me 20 rows from Database1..MY_TABLE. ANY 20 rows. The overall query does not fail.
Why? It would have failed in SQL2000 (from which I have recently migrated)
The subquery Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE'
my_table_2 might contain one row with other_field='value' . I think other_field to my_table_2.
if outer query field is referenced in a subquery query the subquery will fire for n outer no of rows.
So that is the reason why you get 20 rows. If the subquery returns 2 rows then your query will fail.
In the absence of table aliases to determine the table source for each column, SQL Server assumes that THE_ID in the subquery is sourced from the outer FROM list. Hence the filter becomes WHERE THE_ID = THE_ID AND EXISTS (SELECT 1 FROM Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE').
Look at this statement:
WHERE EXISTS (Select THE_ID, t2.OTHER_FIELD from Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE'). While not particularly useful, it's valid.
ditto
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2010 at 9:42 am
Big thank you to Chris Morris - Wow....I'm genuinely surprised. Didn't think after this many years I'd be caught out by sql syntax. I always use aliases in code, but literally threw this query into SSMS out of my head to get data I was looking for, and couldn't believe the results.
Has this behaviour changed since SQL 2000? Must have missed this when doing the jump from 2000 to 2008.
Brian
September 27, 2010 at 9:54 am
It's the same in 2k. Frankly I find the behaviour inconsistent and it's a common mistake.
If it will accept that an un-alaised column can belong to either the inner or outer query, then it should also throw an ambiguous column name exception if both tables have a column of that name and you don't provide an alias.
September 27, 2010 at 10:10 am
True Howard, except that in my case, the second table *didn't* have a column of that name, but when typing the query, I had thought that it did.
Therefore where it interpreted the column name in the subquery as belonging to the outer table, there was nowhere else it could have belonged to.
Brian
September 27, 2010 at 10:34 am
Brian McGee-355525 (9/27/2010)
True Howard, except that in my case, the second table *didn't* have a column of that name, but when typing the query, I had thought that it did.Therefore where it interpreted the column name in the subquery as belonging to the outer table, there was nowhere else it could have belonged to.
Brian
Yes, I know. But if you were to add the column to that table without changing the query it would not produce an ambiguous column error the next time it ran, it would reference an entirely different column to give an entirely different result. I find that inconsistent with the way column name collision is dealt with in every other area within SQL Server.
September 27, 2010 at 10:48 am
Yes, that's true. Very valid point!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply