Difference between cross apply and inner join based on below example

Question

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.

Answer 1

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.