Why isn't my CROSS APPLY working like an inner join?

  • My understanding of CROSS APPLY is that it should behave like an inner join.

    In the example I've created, I still get records with no match from my "function" table.

    This is the first time I've posted code here so please be patient if I screw it up a couple of times.

    PS: The trailing commas in my lists are not an issue.

    create table #tmpClients

    (

    ClientKey int,

    ClientName varchar(4)

    )

    insert #tmpClients

    select 1,'Joe' union

    select 2,'Jim' union

    Select 3,'Jane' union

    Select 4,'Jon'

    create table #tmpAppointments

    (

    AppointmentKey int identity,

    ClientKey int,

    AppointmentDate datetime

    )

    insert #tmpAppointments

    select 2,'1/1/2012' union

    select 3,'2/1/2012' union

    select 2,'3/1/2012' union

    select 3,'4/1/2012'

    select ClientName, a.AppointmentList

    from #tmpClients c

    cross apply

    (

    select convert(char(10),ap.AppointmentDate,101) + ',' as [text()]

    from #tmpAppointments ap

    where c.ClientKey = ap.ClientKey

    order by ap.AppointmentDate

    for XML path('')

    ) a (AppointmentList)

    order by 1

    drop table #tmpClients, #tmpAppointments

  • My understanding of CROSS APPLY is that it should behave like an inner join.

    CROSS APPLY behaves like a LEFT OUTER JOIN, so you will a row for each Client that has no appointments.

    Just add "where a.AppointmentList is not null"

    SQL = Scarcely Qualifies as a Language

  • I believe CROSS APPLY is more like a cartesian product. If you want the join effect could you try something like this.

    select ClientName, AppointmentList

    from #tmpClients c

    JOIN (

    SELECT DISTINCTap.ClientKey, STUFF((SELECT ',' + convert(char(10),t1.AppointmentDate,101) FROM #tmpAppointments t1 WHERE t1.ClientKey = ap.ClientKey

    FOR XML PATH('')),1,1, '') AS AppointmentList

    FROM #tmpAppointments ap

    ) a ON

    a.ClientKey = c.ClientKey

    It would also have the side effect of no trailing comma in case you want to ultimately get rid of that.

    Cliff

  • Have a look at this article.

    http://www.sqlservercentral.com/articles/APPLY/69954/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (12/14/2011)


    Have a look at this article.

    http://www.sqlservercentral.com/articles/APPLY/69954/

    That is part 2, the first part is here: http://www.sqlservercentral.com/articles/APPLY/69953/

  • SQL Kiwi (12/14/2011)


    SQLRNNR (12/14/2011)


    Have a look at this article.

    http://www.sqlservercentral.com/articles/APPLY/69954/

    That is part 2, the first part is here: http://www.sqlservercentral.com/articles/APPLY/69953/

    Yeah - I went with part deux due to the comment about the Cross being a Left Join. OP needs to understand that is not a hard and fast rule.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Carl Federl (12/14/2011)


    My understanding of CROSS APPLY is that it should behave like an inner join.

    CROSS APPLY behaves like a LEFT OUTER JOIN, so you will a row for each Client that has no appointments.

    Just add "where a.AppointmentList is not null"

    That's outer apply, actually, as far as I know. It's odd that cross apply is giving this result.

    Poking away...

    This, for example, acts correctly like a join:

    select ClientName, a.AppointmentList

    from #tmpClients c

    cross apply

    (

    select convert(char(10),ap.AppointmentDate,101) + ',' as [text()]

    from #tmpAppointments ap

    where c.ClientKey = ap.ClientKey

    --order by ap.AppointmentDate

    --for XML path('')

    ) a (AppointmentList)

    order by 1

    Thus, it's got something to do with the FOR XML operation. I haven't goofed with it for a while so I'm getting syntax errors but you're going to need something similar to this:

    select ClientName, b.AppointmentList

    from #tmpClients c

    cross apply

    (select

    a.AppointmentList

    FROM

    (

    select ap.ClientKey, convert(char(10),ap.AppointmentDate,101) + ',' as [text()]

    from #tmpAppointments ap

    order by ap.AppointmentDate

    for XML path('')

    ) a (ClientKey, AppointmentList)

    where

    c.ClientKey = a.ClientKey

    ) AS b

    order by

    1

    However, that code is invalid, I've got it all tangled up and will need to toy with it a bit.

    CROSS APPLY does = Join... except when FOR XML gets involved, apparently... Must have something to do with creating a NULL row for non-included xml components.

    EDIT: Which is discussed in a lot more detail and more effectively in Paul's articles that they both he and Jason linked. Look near the end of the 'second' article. Ima gonna go be quiet now. :hehe: (Teach me to leave windows open for too long without refreshing the thread...)


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • That's outer apply, actually, as far as I know. It's odd that cross apply is giving this result.

    Thanks for the validation, Craig.:-) I read both parts one and two of that article prior to my post. It does appear that CROSS APPLY = INNER JOIN is not an absolute. Be nice to know why.

  • jshahan (12/15/2011)


    I read both parts one and two of that article prior to my post. It does appear that CROSS APPLY = INNER JOIN is not an absolute. Be nice to know why.

    This is just the semantics of SQL - specifically the behaviour of scalar aggregates on empty sets - as discussed in the Extra Credit section of part 2.

    SELECT T.v FROM (SELECT 1 WHERE 0 = 1 FOR XML PATH('')) AS T (v) -- single row & column containing NULL

    SELECT 1 WHERE 0 = 1 FOR XML PATH('') -- empty result set (no rows, no columns)

    FOR XML is an aggregate:

    DECLARE @T1 TABLE

    (

    col1 INTEGER NOT NULL

    )

    DECLARE @T2 TABLE

    (

    col1 INTEGER NOT NULL,

    col2 CHAR(1) NOT NULL

    )

    INSERT @T1

    (col1)

    VALUES

    (1),

    (2)

    INSERT @T2

    (col1, col2)

    VALUES

    (1, 'a'),

    (1, 'b')

    SELECT

    *

    FROM @T1 AS t1

    CROSS APPLY

    (

    -- Scalar aggregate

    SELECT

    ',' + t2.col2

    FROM @T2 AS t2

    WHERE

    t2.col1 = t1.col1

    FOR XML PATH('')

    ) AS ca (csv)

    SELECT

    *

    FROM @T1 AS t1

    CROSS APPLY

    (

    -- Scalar aggregate

    SELECT

    MAX(t2.col2)

    FROM @T2 AS t2

    WHERE

    t2.col1 = t1.col1

    ) AS ca (csv)

    The logical comparison section at the top of part two attempts to make the distinction between JOIN and APPLY clear. The important point is whether the function returns a row or not. Where the function is a scalar aggregate, a row is always returned, as is required by SQL. In cases where the function does not return a row, you need an OUTER APPLY.

  • Thanks, Paul. I re-read the Extra Credit section of part II (great job by the way and thank you) and your last response and believe I can paraphrase it as: If your function returns a NULL in certain conditions by design (as in the case of the FOR XML aggregate), then you will get a row returned because the NULL is a valid as any other value.

    Am I close?

  • jshahan (12/15/2011)


    Thanks, Paul. I re-read the Extra Credit section of part II (great job by the way and thank you) and your last response and believe I can paraphrase it as: If your function returns a NULL in certain conditions by design (as in the case of the FOR XML aggregate), then you will get a row returned because the NULL is a valid as any other value.

    Am I close?

    Yes 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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