Correlated Joins Using "Apply"

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

    Gregory A Jackson MBA, CSM

  • 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....


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

  • Alex,

    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

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

  • 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.


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

    Would this be something for OLTP, or just OLAP?

  • 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

  • Hmm,

    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.


  • 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.

  • 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?

  • 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.


  • 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

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



  • Mark,

    Could you post your code again?


    Gregory A Jackson MBA, CSM

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

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