Columns in Sub Queries

Question

Post reply

Columns in Sub Queries

Farlzy

Mr or Mrs. 500

Points: 561
More actions
February 22, 2014 at 8:02 pm

#286002

Comments posted to this topic are about the item Columns in Sub Queries
February 24, 2014 at 12:26 am

This was removed by the editor as SPAM

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Didn't know that, :^)

Thanks, interesting question anyway.

Igor Micev,My blog: www.igormicev.com

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

Interesting question - thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

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 More actions · Answer 4

Ford Fairlane (2/23/2014)
Interesting question - thanks

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Umer Akram

SSC-Insane

Points: 22209

February 23, 2014 at 11:51 pm

#1691415

nice question ... thanks for sharing

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

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: 15320 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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: 287026 More actions · Answer 10

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 11

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: 287026 More actions · Answer 12

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Cadavre SSC-Forever Points: 41690 More actions · Answer 13

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/