September 6, 2012 at 6:14 am
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
September 6, 2012 at 6:29 am
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:
SRS
AlphaD12341001
charlieD12341001
betaA11224001
harryA11224001
AlphaD12342001
charlieD12342001
bravoC13425001
betaA11223001
harryA11223001
If you change the values in table #B to produce non-unique combinations, you still get 9 results:
SRS
AlphaD12341001
charlieD12341001
betaA11221001
harryA11221001
AlphaD12341001
charlieD12341001
bravoC13421001
betaA11221001
harryA11221001
September 6, 2012 at 6:33 am
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.
September 6, 2012 at 6:44 am
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. 😀
September 6, 2012 at 6:45 am
Nice question. I've only ever used it with a TVF... It is interesting to know it works with subqueries as well.
September 6, 2012 at 6:48 am
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.
September 6, 2012 at 7:54 am
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: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.
September 6, 2012 at 8:03 am
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) == 1The 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.
September 6, 2012 at 8:14 am
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!
September 6, 2012 at 8:18 am
September 6, 2012 at 8:34 am
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 🙂
September 6, 2012 at 8:45 am
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?)
September 6, 2012 at 9:01 am
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.
September 6, 2012 at 9:02 am
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.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply