APPLY

Question

APPLY

Steve Jones - SSC Editor

SSC Guru

Points: 736232
More actions
October 7, 2014 at 9:07 pm

#74772

Comments posted to this topic are about the item APPLY
October 8, 2014 at 4:01 am

This was removed by the editor as SPAM

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

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

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

Easy one, thanks.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

I was thinking it was too easy, so I started looking for the trickery in the phrasing of the question. Thanks.

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

SqlMel SSCrazy Points: 2901 More actions · Answer 3

Very easy one for anybody that has used this operator before, IMO.

Thanks, Steve!

---------------
Mel. 😎

SqlMel SSCrazy Points: 2901 More actions · Answer 4

Ed Wagner (10/8/2014)
I was thinking it was too easy, so I started looking for the trickery in the phrasing of the question. Thanks.

+1

---------------
Mel. 😎

Brian.Klinect Mr or Mrs. 500 Points: 592 More actions · Answer 5

Why was an apply used and not a left join? Is there a reason beyond just needing an example?

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

Great question! I love me some APPLY.

Everything is awesome!

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 7

While I understand what both APPLYs do, I believe I misunderstood the question. I didn't realize "Which type of apply will return the IT department for this query?" meant which APPLY will return all of the departments. I've reread several times and still don't understand the question.

Aigle de Guerre!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Thank you for the post, Steve, interesting one.

(I have not used OUTER apply and was sure of the CROSS, so the choice was easy for me, I made the selection, then wrote the sql statement to make sure I am right and then submitted. Full Fun :-))

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Meow Now (10/8/2014)
While I understand what both APPLYs do, I believe I misunderstood the question. I didn't realize "Which type of apply will return the IT department for this query?" meant which APPLY will return all of the departments. I've reread several times and still don't understand the question.

It doesn't say "the IT department and nothing else", does it? Surely it's reasonable for it to not to mean what it doesn't say? If one query doesn't return th IT dpartment and teh other does return it along with other things, which one returns it? The answer certainly isn't neither! So I reckon it's chrystal clear what the question means - - certainly it's a lot clearer then a lot of requirements specifications and government regulations I've had to deal with over the years.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Brian.Klinect (10/8/2014)
Why was an apply used and not a left join? Is there a reason beyond just needing an example?

Given that most SQL developers appear to consider APPLY as some sort of esoteric voodoo that no normal human being could ever understand it I recon it's a very good idea to have some extremely simple questions of the day (like this one - well done Steve). I guess the general avoidance of APPLY is because in the simplest cases it is equivalent to a join, so people only usually see it in non-simple cases and that makes them think it is too complex; so letting them see some simple cases may be very educational.

Tom

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 11

Nice question, Steve, thanks. I was familiar with Cross Apply, but not Outer Apply, although it makes sense for both to be available, and Outer Apply to be similar to an Outer Join. It would have been nice to have said something in the answer about the difference between Outer Join and Outer Apply, or for the question to be one which could only be done using Outer Apply, which is not the case for this query. Maybe you could rework this in that way for a future QotD? 🙂

Steve Jones - SSC Editor SSC Guru Points: 736232 More actions · Answer 12

Brian.Klinect (10/8/2014)
Why was an apply used and not a left join? Is there a reason beyond just needing an example?

yes. The inner/outer joins and APPLY are similar (as evidenced by both in the FROM clause), but there are differences. Rather than try to make this complex, I wanted to use a familiar reference to help people focus on the CROSS/OUTER apply rather than the query.

jkoppin SSC Enthusiast Points: 184 More actions · Answer 13

But this isn't a requirements specifications or government regulation. Not to be combative, but I am in agreement on this one.

It is because when you only have two employees and one is in the IT department and the other is in the Finance department, if you only specify IT in a question that (IMO) is supposed to be for learning purposes, it is a completely reasonable assumption that it means only IT...especially if you have been burned by the counter assumption on similarly worded previous QotD's here, which I'm sure many of us have. In my opinion communication is the hardest part about IT because of things like this, where it is obvious to one person, and not to another because the wording could possibly have multiple meanings. Just go the extra 2ft and be more specific - it isn't difficult 🙂