Correlated Joins Using "Apply"

  • GregoryAJackson


    Points: 2794

    Comments posted to this topic are about the item Correlated Joins Using "Apply"

    Gregory A Jackson MBA, CSM

  • Michele Ballarin


    Points: 17

    i'm unable to read it

    i try to turn the video, but SQLscritp turn whit the screen and the only way to read the script is to copy and paste into Notepad or similar....


  • Alex 469216

    Valued Member

    Points: 60

    it could be a descent article had it been written 2 years ago.

  • GregoryAJackson


    Points: 2794


    I wish I had known of this feature 2 years ago but it's one I missed until recently. My hope is that there are others out there that had been missing this also so now they can leverage it.



    Gregory A Jackson MBA, CSM

  • wordracr

    SSC Enthusiast

    Points: 176

    This is possible in sql 2000 as well.

    select 1 as id, '1st' as position into #data union all

    select 1 as id, '2nd' as position union all

    select 1 as id, '3rd' as position union all

    select 1 as id, '4th' as position union all

    select 1 as id, '5th' as position union all

    select 2 as id, '1st' as position union all

    select 2 as id, '2nd' as position union all

    select 2 as id, '3rd' as position union all

    select 2 as id, '4th' as position union all

    select 2 as id, '5th' as position

    select *

    from #data d1

    where position in (select top 3 position from #data d2 where = order by position)

  • DavidSimpson

    Hall of Fame

    Points: 3552

    I disagree with Alex, it does not matter if SQL Server 2005 was release 2 minutes ago or 2 years ago, there is always something new to learn about SQL Server. I think this was a good article pointing out a feature introduced in SQL Server 2005. I have read about APPLY but have not used it yet which makes it easily forgettable :blink: , so I appreciate an article that reminds me of different ways of getting a job done.


  • Michael Clark-374593

    SSC Rookie

    Points: 29

    What's is the performance hit from using the UDF?

    Would this be something for OLTP, or just OLAP?

  • GregoryAJackson


    Points: 2794

    Hi Michael,

    I'm not sure of the performance hit of the udf, I'd have to test the various query plans, etc.

    The maintenace issue is what I'd want to stay away from (Having to create another object in SQL, Version control it, script it, maintain it, etc)


    Gregory A Jackson MBA, CSM

  • MudLuck


    Points: 2301


    The first thought that came to me isn't this what I've use derived tables and identities for in the past.

    I won't guarantee performance want suffer of be less then using apply because I'm still on sql 2000 :-(, but this is how I would solve the problem.

    In the derived table query I would show the transaction id on the top three items and there dates sorted by transaction id and dates I would then have another query above it in the same derived table that mapped those rows to there identities and then use those identities as the join condition out of the derived table to return my max top 3 rows per transaction ID.

    I haven't seen your schema put I'm pretty sure this can be done.


  • Peter E. Kierstead

    SSCarpal Tunnel

    Points: 4114

    We sometimes create our own "full-Text" indexes instead of using SQL's Full-Text feature because we have non-standard requirements...

    I'll create a Multi-Statement table-valued function that parses a string parameter into a table of terms.

    I'll Cross Apply this function to a database table passing the column (or appended columns) I wish to parse into a full-text index, outputing the resultant UDF-generated table along with the primary key of the database table row.

    I'll index this table appropriately, and wallah!, home-grown full-text index...

    Tastes great... less filling...

    I leave the exercise of integration up to you to help burn the calories gained from ingesting this full-bodied brew!

    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Having done some amount of testing on this - while this scenario is certainly convenient in some scenarios, it has a tendency to perform on par with CSQ's, so it will suffer rather severely on large datasets. It unfortunately just seems to be a cleaner way to write a Correlated sub-query, but with the same kinds of perf challenges the CSQ's had (it forces row-by-row evaluation, the logical reads tend to get out of control, etc...)

    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Anipaul


    Points: 24681

    Yes I agree you can always learn new things. Even you can learn new things from your juniors. Some people may know these feature but this article is good for many people.


  • Yelena Varshal


    Points: 34233

    This is an excellent article by itself and even better that it suggests alternative solutions by other authors. I will try them all.

    When you need to do something quick, you normally use most familiar tools and if you don't have to run this query often, performance does not matter. I needed to get top 10 calls for each contact and pivot call dates to the output columns. Well, I used s cursor with the outer loop going from contact to contact and the inner loop moving from call to call. The line number for the call for a certain customer also served as a part of the column name in the update statement:

    select @strUpdate ='update ##TempTableCalls set Call_'+ convert(nvarchar(10),@LINE_NO) + ' = ' (the rest of the line going here)

    The temp table with the columns like Call_1, Call_2 was created in advance and populated with something additional before adding calls.

    After the update string for a call was composed, I used

    Exec sp_executesql @strUpdate

    Worked fine and reliable, I had to use it only twice, so not performance concerns, but I spent a lot of time writing it.


    Regards,Yelena Varsha

  • mark rubanovich


    Points: 5

    Was not it easy to use more simple sql, like this one:



  • GregoryAJackson


    Points: 2794


    Could you post your code again?


    Gregory A Jackson MBA, CSM

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

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