April 2, 2018 at 9:25 pm
Comments posted to this topic are about the item select in subquery
April 2, 2018 at 11:47 pm
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”
April 3, 2018 at 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
);
April 3, 2018 at 2:19 am
And this is why you should ALWAYS use two-part column names, preferably with well chosen table aliases.
April 3, 2018 at 2:26 am
SimonH - Tuesday, April 3, 2018 12:52 AMI 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.
April 3, 2018 at 7:07 am
Rune Bivrin - Tuesday, April 3, 2018 2:19 AMAnd 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!
April 3, 2018 at 7:38 am
RoNoS - Tuesday, April 3, 2018 2:26 AMSimonH - Tuesday, April 3, 2018 12:52 AMI 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.
April 3, 2018 at 8:43 am
RoNoS - Monday, April 2, 2018 9:25 PMComments 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.
April 3, 2018 at 9:06 am
qbrt - Tuesday, April 3, 2018 8:43 AMRoNoS - Monday, April 2, 2018 9:25 PMComments posted to this topic are about the item select in subqueryAnd 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.
April 3, 2018 at 10:39 am
Lynn Pettis - Tuesday, April 3, 2018 9:06 AMqbrt - Tuesday, April 3, 2018 8:43 AMRoNoS - Monday, April 2, 2018 9:25 PMComments posted to this topic are about the item select in subqueryAnd 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.
April 3, 2018 at 11:09 pm
Lynn Pettis - Tuesday, April 3, 2018 9:06 AMqbrt - Tuesday, April 3, 2018 8:43 AMRoNoS - Monday, April 2, 2018 9:25 PMComments posted to this topic are about the item select in subqueryAnd 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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy