Columns in Sub Queries

  • Farlzy

    Mr or Mrs. 500

    Points: 561

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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Didn't know that, :^)

    Thanks, interesting question anyway.

    Igor Micev,
    My blog: www.igormicev.com

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Interesting question - thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • free_mascot

    One Orange Chip

    Points: 27168

    Good one!

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

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Ford Fairlane (2/23/2014)


    Interesting question - thanks

  • twin.devil

    SSC-Insane

    Points: 22208

    nice question ... thanks for sharing

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Great question, thanks.

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

  • paul.knibbs

    SSCoach

    Points: 15270

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

  • Gazareth

    One Orange Chip

    Points: 27737

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

    One of the reasons I always always use aliases 🙂

  • Cadavre

    SSC-Forever

    Points: 41690

    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/

  • Ed Wagner

    SSC Guru

    Points: 286988

    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.

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    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?

  • Ed Wagner

    SSC Guru

    Points: 286988

    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.

  • Cadavre

    SSC-Forever

    Points: 41690

    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