December 14, 2011 at 3:35 pm
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
December 14, 2011 at 4:15 pm
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
December 14, 2011 at 4:37 pm
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
December 14, 2011 at 4:39 pm
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
December 14, 2011 at 4:47 pm
SQLRNNR (12/14/2011)
Have a look at this article.
That is part 2, the first part is here: http://www.sqlservercentral.com/articles/APPLY/69953/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 14, 2011 at 4:51 pm
SQL Kiwi (12/14/2011)
SQLRNNR (12/14/2011)
Have a look at this article.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
December 14, 2011 at 4:59 pm
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...)
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
December 15, 2011 at 6:14 am
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.
December 15, 2011 at 7:07 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 15, 2011 at 7:26 am
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?
December 15, 2011 at 7:47 am
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 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply