CROSS APPLY and OUTER APPLY Operators in SQL Server(TSQL)

In SQL Server, we can join two tables with JOIN operators. You can read the “Join Types in SQL Server” article.

With the CROSS APPLY and OUTER APPLY operators, we can join a table and a result set returned by a table-valued function.

Difference Between CROSS APPLY and OUTER APPLY:

In the CROSS APPLY operator, only the matching records between the two sources(a table and a table valued function) returns. That is, like INNER JOIN.

In the OUTER APPLY operator, all records in the table returns as a result even if they do not match the result set of the table valued function. That is, like LEFT OUTER JOIN.

To better understand the CROSS APPLY and OUTER APPLY operators, let’s first create two sample tables and insert a few records into these tables.

Now let’s create a table-valued function that reads records from the FamousFood table according to the CityID column. We will use this table valued function in CROSS APPLY and OUTER APPLY examples.

Get_Famous_Food_Using_CityID table valued fonksiyonunu kullanarak CityID değeri 42 olan kayıtları listeleyelim.

Let’s list the records with the CityID value “42” using the Get_Famous_Food_Using_CityID table valued function.

CROSS APPLY and INNER JOIN:

First, let’s join these two tables with the inner join method. A result returns as follows.

Then use the CROSS APPLY operator to join the City table and the Get_Famous_Food_Using_CityID table valued function.

As you can see, we get the same result as the join of the City and FamousFood tables, by joining the City table and the Get_Famous_Food_Using_CityID table valued function with the CROSS APPLY operator.

LEFT OUTER JOIN and OUTER APPLY:

First, we add a record to City and FamousFood tables that are not related to each other. Thus, we aim to have mismatched records.

And we join the two tables with the FULL OUTER JOIN method as follows.

As you can see, when we join the two tables with the FULL OUTER JOIN method, all records in the two tables returned as a result, although the records did not match.

When we join two tables with the LEFT OUTER JOIN method, as you can see below, all records in the City table return as a result even if they don’t match the records in the FamousFood table. However, records in the FamousFood table that does not match the records in the City table does not return as a result.

We can do the same by using the OUTER APPLY operator as follows.