Is there a difference in performance between INNER JOIN and CROSS APPLY?

Question

I am starting studies on SQL SERVER and some places teach to use the INNER JOIN to make the junction of two tables. However, there are some other places that teach via CROSS APPLY.

Answer 1

Douglas, INNER JOIN compares the rows of two tables based on a condition specified in the query. This type of Join is used at the intersection of lines that has the same value in a column (or more than one column). The returned rows are only those that are not two tables.

CROSS JOIN is also called the Cartesian Product, where all rows in the table on the left are returned and each of these lines is combined with all rows in the table on the right. In practical terms, if the table on the left has 10 rows and the table on the right has 100 rows, a cross Join will return 1000 rows.

CROSS APPLY is like a cross Join in terms of the result it produces, but is only used with a function. With APPLY, one of the inputs (on the right) is not physically materialized in the database because its output depends on input parameters, such as a table-Valued Function, as in the example below.

SELECT SP.SalesYTD, P.FirstName, P.LastName, P.JobTitle FROM Sales.SalesPerson AS SP CROSS APPLY dbo.ufnGetContactInformation (SP.BusinessEntityID) AS P;

It is not possible to compare the performance between INNER JOIN and CROSS APPLY because they have different purposes.