select in subquery

  • Comments posted to this topic are about the item select in subquery

  • Nice one, thanks Ron
    This has bitten me in the past (especially when it comes to maintaining someone else's code)

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    โ€œlibera tute vulgaris exโ€

  • I twill work if you don't use two part naming convention but reference the correct Column in the Sub Query.

    i.e.

    IN (

    SELECT schema_id FROM schemas_cte

    );

    Instead of this

    IN

     (
    SELECT schemaid FROM schemas_cte
    );

  • And this is why you should ALWAYS use two-part column names, preferably with well chosen table aliases.


    Just because you're right doesn't mean everybody else is wrong.

  • SimonH - Tuesday, April 3, 2018 12:52 AM

    I twill work if you don't use two part naming convention but reference the correct Column in the Sub Query.

    i.e.

    IN (

    SELECT schema_id FROM schemas_cte

    );

    Instead of this

    IN

     (
    SELECT schemaid FROM schemas_cte
    );

    I know, I had a typo myself but wondered why this still works and not gave an error.

  • Rune Bivrin - Tuesday, April 3, 2018 2:19 AM

    And this is why you should ALWAYS use two-part column names, preferably with well chosen table aliases.

    I could not agree more.

    Fascinating question. 
    Thanks, RoNoS!

  • RoNoS - Tuesday, April 3, 2018 2:26 AM

    SimonH - Tuesday, April 3, 2018 12:52 AM

    I twill work if you don't use two part naming convention but reference the correct Column in the Sub Query.

    i.e.

    IN (

    SELECT schema_id FROM schemas_cte

    );

    Instead of this

    IN

     (
    SELECT schemaid FROM schemas_cte
    );

    I know, I had a typo myself but wondered why this still works and not gave an error.

    I did not read the explanation after getting the answer correct, but the simple reason it worked is due to how the sub-query works.  Since SQL Server could not find the column name in the table in sub-query it went to the outer query and found the column name there and used it to resolve the SELECT statement in the sub-query.  Since there were two values in the outer query table, it returned two rows in the sub-query.

  • RoNoS - Monday, April 2, 2018 9:25 PM

    Comments posted to this topic are about the item select in subquery

    And this is one of the many reasons most Data Analyst people I work with can't stand T-SQL. ๐Ÿ™‚ They tend to use this type of construct all the time.

  • qbrt - Tuesday, April 3, 2018 8:43 AM

    RoNoS - Monday, April 2, 2018 9:25 PM

    Comments posted to this topic are about the item select in subquery

    And this is one of the many reasons most Data Analyst people I work with can't stand T-SQL. ๐Ÿ™‚ They tend to use this type of construct all the time.

    I don't have access to other systems like Oracle, PostgreSQL, MySQL, but I wouldn't be surprised if correlated sub-queries worked the same way in them.  Also, if you follow best practices, you don't run into this problem.  One of the reasons I keep pushing using table aliases and two-part naming conventions even on single table queries.

  • Lynn Pettis - Tuesday, April 3, 2018 9:06 AM

    qbrt - Tuesday, April 3, 2018 8:43 AM

    RoNoS - Monday, April 2, 2018 9:25 PM

    Comments posted to this topic are about the item select in subquery

    And this is one of the many reasons most Data Analyst people I work with can't stand T-SQL. ๐Ÿ™‚ They tend to use this type of construct all the time.

    I don't have access to other systems like Oracle, PostgreSQL, MySQL, but I wouldn't be surprised if correlated sub-queries worked the same way in them.  Also, if you follow best practices, you don't run into this problem.  One of the reasons I keep pushing using table aliases and two-part naming conventions even on single table queries.

    Yes, agree with your statement here. Following some good practices minimizes many of these gotchas.

  • Lynn Pettis - Tuesday, April 3, 2018 9:06 AM

    qbrt - Tuesday, April 3, 2018 8:43 AM

    RoNoS - Monday, April 2, 2018 9:25 PM

    Comments posted to this topic are about the item select in subquery

    And this is one of the many reasons most Data Analyst people I work with can't stand T-SQL. ๐Ÿ™‚ They tend to use this type of construct all the time.

    I don't have access to other systems like Oracle, PostgreSQL, MySQL, but I wouldn't be surprised if correlated sub-queries worked the same way in them.  Also, if you follow best practices, you don't run into this problem.  One of the reasons I keep pushing using table aliases and two-part naming conventions even on single table queries.

    Having worked on DB2 & Oracle, i can confirm that correlated subqueries work in exactly the same way.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    โ€œlibera tute vulgaris exโ€

Viewing 11 posts - 1 through 11 (of 11 total)

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