APPLY - 1

  • Mike Is Here

    Hall of Fame

    Points: 3348

    I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

    SELECT

    tblA.S

    , tblA.R

    , tblB.S

    FROM

    #A tblA

    INNER JOIN

    #B tblB

    ON tblA.R = tblB.R

  • sknox

    SSChampion

    Points: 12243

    derek.colley (9/6/2012)


    Good question, I had it in my head that CROSS APPLY acted like an INNER JOIN and OUTER APPLY acted like an OUTER JOIN, but in fact the CROSS apply would return unique combinations, excluding NULLs on the right - hence my answer of 5.

    Never mind, another QOTW chance tomorrow...

    CROSS APPLY does act like an INNER JOIN, but not like a DISTINCT. So it does exclude the NULLS (you can see that because there are no rows for Doug in the result set), but it does not only return unique combinations. But that is moot because the result set for this question is all unique combinations anyway:

    S R S

    Alpha D1234 1001

    charlie D1234 1001

    beta A1122 4001

    harry A1122 4001

    Alpha D1234 2001

    charlie D1234 2001

    bravo C1342 5001

    beta A1122 3001

    harry A1122 3001

    If you change the values in table #B to produce non-unique combinations, you still get 9 results:

    S R S

    Alpha D1234 1001

    charlie D1234 1001

    beta A1122 1001

    harry A1122 1001

    Alpha D1234 1001

    charlie D1234 1001

    bravo C1342 1001

    beta A1122 1001

    harry A1122 1001

  • sknox

    SSChampion

    Points: 12243

    mbova407 (9/6/2012)


    I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

    SELECT

    tblA.S

    , tblA.R

    , tblB.S

    FROM

    #A tblA

    INNER JOIN

    #B tblB

    ON tblA.R = tblB.R

    This particular example isn't, which is why I wouldn't code this with a CROSS APPLY in production. But you can't JOIN to a table-value function (which is what CROSS APPLY was written for), and you can also put things like TOP X in a subquery with CROSS APPLY to limit the results from the right table, which you can't do in an INNER JOIN. Since this QotD is APPLY - 1, I'm guessing (and hoping) bitbucket has some more detailed examples of how to use CROSS APPLY coming in future questions.

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thank you for the excellent question this morning Ron. I had to dig around in my head this morning for a while to figure it out, but caffeine makes it possible. No zombie mode for me today and you've given me a few things to read back up on. 😀



    Everything is awesome!

  • sestell1

    SSChampion

    Points: 10230

    Nice question. I've only ever used it with a TVF... It is interesting to know it works with subqueries as well.

  • Luis Cazares

    SSC Guru

    Points: 183583

    Stewart "Arturius" Campbell (9/6/2012)


    Good question, Ron, thanks.

    There's an extra "=", other than that is valid.

    It was a good question, but I still can't find a great use of APPLY in my current juob. Hopefully with the next questions I get more examples.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • derek.colley

    SSCrazy Eights

    Points: 8040

    Bob - CAST statement in signature...

    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1

    The statement on the left is equal to the statement on the right, i.e. 1.

    The double-equals is dev-jargon for 'equals' rather than 'assign-to', see http://www.developer.com/lang/other/article.php/604441/Double-vs-Single-Equal-Sign.htm

    Not strictly mathematically correct, perhaps, but it's a foible I'm comfortable with.

    If you preface the left side of the equation with 'SELECT' and run in SSMS, you'll see what I mean.

    Derek.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the great question Ron. Eagerly anticipating the follow on questions.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • (Bob Brown)

    SSCrazy

    Points: 2705

    derek.colley (9/6/2012)


    Bob - CAST statement in signature...

    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1

    The statement on the left is equal to the statement on the right, i.e. 1.

    The double-equals is dev-jargon for 'equals' rather than 'assign-to', see http://www.developer.com/lang/other/article.php/604441/Double-vs-Single-Equal-Sign.htm

    Not strictly mathematically correct, perhaps, but it's a foible I'm comfortable with.

    If you preface the left side of the equation with 'SELECT' and run in SSMS, you'll see what I mean.

    Derek.

    Thanks for the explanation Derek.

  • tilew-948340

    Hall of Fame

    Points: 3431

    This was a good question for me: I did not really knew about APPLY so I had to make some test to compare with JOIN: CROSS APPLY vs INNER JOIN and OUTER APPLY vs LEFT/RIGHT JOIN. I got the answer wrong, but I learn a lot.

    Thanks!

  • EL Jerry

    SSCertifiable

    Points: 7053

    Thank you for the question Ron, made me think a little bit.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Narud

    SSCrazy

    Points: 2826

    mbova407 (9/6/2012)


    I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

    SELECT

    tblA.S

    , tblA.R

    , tblB.S

    FROM

    #A tblA

    INNER JOIN

    #B tblB

    ON tblA.R = tblB.R

    I think that is because CROSS APPLY is more like not use explicit joins, as in SQL ANSI '92.

    This query returns the same result:

    SELECT tblA.S, tblA.R, tblB.S

    FROM #A tblA

    , #B tblB

    WHERE tblA.R = tblB.R

    And yes, I'm old enough to remember that 🙂

  • Rich Weissler

    Hall of Fame

    Points: 3235

    sknox (9/6/2012)


    CROSS APPLY does act like an INNER JOIN, but not like a DISTINCT.

    In fact, when I pull up the actual query plan SQL used for this CROSS APPLY, it actually performs Nested Loops (Inner Join). I was also amused to discover that it scans #B once, and #A five times (the opposite of the way I read the query). I assume because the optimizer decided this was more efficient? (Or am I misinterpreting?)

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Narud (9/6/2012)


    I think that is because CROSS APPLY is more like not use explicit joins, as in SQL ANSI '92.

    I'd put it differently. The only difference between CROSS APPLY and INNER JOIN is that CROSS APPLY can be followed by a subquery or by a table-valued function that references data from the other table.

    So, in code:

    These are valid joins:

    FROM Table1 AS t INNER JOIN (non-correlated subquery) AS s ON s.Col1 = t.Col1

    FROM Table1 AS t INNER JOIN dbo.MyFunction(constants or variables) AS f ON f.Col1 = t.Col1

    These are NOT valid joins:

    FROM Table1 AS t INNER JOIN (correlated subquery) AS s ON s.Col1 = t.Col1

    FROM Table1 AS t INNER JOIN dbo.MyFunction(t.SomeColumn) AS f ON f.Col1 = t.Col1

    Rewriting them with APPLY makes them valid:

    FROM Table1 AS t CROSS APPLY (correlated subquery WHERE s.Col1 = t.Col1)

    FROM Table1 AS t CROSS APPLY dbo.MyFunction(t.Col1) AS f

    And the valid joins can be rewritten with APPLY as well - though that is in fact quite pointless:

    FROM Table1 AS t CROSS APPLY (non-correlated subquery)

    FROM Table1 AS t CROSS APPLY dbo.MyFunction(constants or variables)

    EDIT: Corrected mistakes sknox pointed out to me.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Rich Weissler (9/6/2012)


    In fact, when I pull up the actual query plan SQL used for this CROSS APPLY, it actually performs Nested Loops (Inner Join). I was also amused to discover that it scans #B once, and #A five times (the opposite of the way I read the query). I assume because the optimizer decided this was more efficient? (Or am I misinterpreting?)

    You are completely right. The optimizer will happily rewrite a cross apply as an inner join, when possible. It will also happily change the order in which tables are joined if it figures that this can help performance.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 16 through 30 (of 44 total)

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