Columns in Sub Queries

  • Comments posted to this topic are about the item Columns in Sub Queries

  • Didn't know that, :^)

    Thanks, interesting question anyway.

    Igor Micev,
    My blog: www.igormicev.com

  • Interesting question - thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Good one!

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Ford Fairlane (2/23/2014)


    Interesting question - thanks

  • nice question ... thanks for sharing

  • This was removed by the editor as SPAM

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Pretty sure I only knew this one because a similar question has been posted before, so I suppose that means I'm learning something... 🙂

  • It's a common gotcha, always good to be reminded of it!

    One of the reasons I always always use aliases 🙂

  • So I understood the intent of the question and got it correct, but bear in mind that on a case-sensitive collation you'd get the "Invalid column name 'CategoryId'." error.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Good questions. I've seen this gotcha rear its ugly head several times. It makes sense once you find it, but finding it is the hard part.

  • 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?

  • 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?

    It tries to match the column to the table and cannot do so because the table isn't specified with a table name or alias. The subquery has access to the parent query, so it tries to match the column there and succeeds. I personally would not use this "in everyday use", but that's how it works. Maybe someone better than I would use it, but I use aliases to explicitly specify where columns get pulled from. It catches me if I make a mistake before it even runs.

  • 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

    BEGIN;

    DROP TABLE #category;

    END;

    IF object_id('tempdb..#product') IS NOT NULL

    BEGIN;

    DROP TABLE #product;

    END;

    SELECT ID, CategoryName

    INTO #category

    FROM (VALUES(1,'Cutlary'),(2,'Glassware'))a(ID, CategoryName);

    SELECT ID, ProductName, CategoryID

    INTO #product

    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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply