select in subquery

Question

select in subquery

RoNoS

SSC Eights!

Points: 956
More actions
April 2, 2018 at 9:25 pm

#330245

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72486 More actions · Answer 1

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”

Simon Hundleby SSCarpal Tunnel Points: 4553 More actions · Answer 2

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
);

Rune Bivrin SSCrazy Eights Points: 8437 More actions · Answer 3

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.

RoNoS SSC Eights! Points: 956 More actions · Answer 4

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.

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 5

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

qbrt SSCrazy Points: 2737 More actions · Answer 7

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

qbrt SSCrazy Points: 2737 More actions · Answer 9

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.

Stewart "Arturius" Campbell SSC Guru Points: 72486 More actions · Answer 10

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”