Below I have Two tables #temp and #temp2.
create table #temp
(
col1 int
)
insert into #temp values(6),(1),(2),(3),(null)
create table #temp2
(
col1 int
)
insert into #temp2 values(1),(2),(1),(2),(3),(null)
And also i have two queries below With INNER JOIN:
SELECT t1.col1,
Sum(t2.col1) AS col1
FROM #temp t1
INNER JOIN #temp2 t2
ON t1.col1 = t2.col1
GROUP BY t1.col1
Result:
col1 col1
1 2
2 4
3 3
And second query is
With CROSS APPLY:
SELECT *
FROM #temp t1
CROSS apply (SELECT Sum(col1) AS col1
FROM #temp2 t2
WHERE t1.col1 = t2.col1) A
Result:
col1 col1
1 2
2 4
3 3
6 NULL
Now, I want to know the difference between CROSS APPLY and INNER JOIN. I Know CROSS APPLY similar like INNER JOIN For each and Every record of #temp(first table) cross apply will execute.But I am getting Different Result Set based on above result sets Can any one Please Explain?
Thanks in advance.
If my understanding of cross apply is correct the reason you are getting different results here is that CROSS APPLY will apply whatever comes after apply (the right operator) to every row in the left operator (#temp). This means that the number of rows in the result will be the same as the number of rows in #temp. Basing my answer off of "The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output." from https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx.
Note that if you really wanted the results to be the same you could change your cross apply query to this:
SELECT *
FROM #temp t1
CROSS apply (SELECT Sum(col1) AS col1
FROM #temp2 t2
WHERE t1.col1 = t2.col1) A
WHERE A.col1 IS NOT NULL
Also, note that INNER JOIN is defined to be where there is a match on both sides. In your case this means that there were only 3 rows. If you had used a LEFT OUTER join instead you would have gotten the same results in this case as the CROSS APPLY.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With