The difference between the cross and the inner connection is based on the bottom 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 the second request

Using 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 is similar to INNER JOIN. For each and every #temp entry (first table), cross is applied. Execute But I get a different result set based on the above result sets. Can anyone clarify?

Thanks in advance.

Answer 1

If my understanding of cross-application is true, the reason you get different results is because CROSS APPLY will apply everything that comes after application (the right operator) to each line in the left operator (#temp). This means that the number of lines in the result will be the same as the number of lines in #temp. Based on my answer: "The function performed by the table acts as the right input, and the expression of the external table acts as the left input. The correct input is evaluated for each row from the left input, and the resulting rows are combined for the final output." from https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx .

Note that if you really want the results to be the same, you can change your cross apply query:

 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 an INNER JOIN is defined as a match on both sides. In your case, this means that there are only 3 lines. If you used the LEFT OUTER compound, instead you would get the same results in this case as CROSS APPLY.