Cross Apply

Microsoft introduced the APPLY operator in SQL Server 2005. The APPLY operator is similar to the T-SQL JOIN clause as it also allows you to join two tables – for example, you can join an outer table with an inner table. The APPLY operator is a good option when, on one side, we have a table-evaluated expression that we want to evaluate for each row from the table we have on another side. So, the right-side table is processed for each row of the left-side table. The left-side table is evaluated first, and then the right-side table is evaluated against each row of the left-side table to generate the final result set. The final result set includes all columns from both tables.

The APPLY operator has two variations:

• CROSS APPLY
• OUTER APPLY

CROSS APPLY

CROSS APPLY is similar to INNER JOIN, but can also be used to join table-evaluated functions with SQL Tables. CROSS APPLY’s final output consists of records matching between the output of a table-evaluated function and an SQL Table.

OUTER APPLY

OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY’s final output contains all records from the left-side table or table-evaluated function, even if they don’t match with the records in the right-side table or table-valued function.

-----

Starting with SQL Server 2005, you can use the APPLY operator in a Transact-SQL query to join a table to a table-valued function so the function is evoked for each row returned from the table. For example, you might create a query that returns a list of employees from the Employee table. For each employee, you also want to return a list of the individual job positions that person has held in the company.

You can create a function that retrieves the employees’ positions and then evoke that function for each row returned from the Employee table. Your result set will then include a row for each position that an employee has held. For instance, if John has worked as a salesman and a regional supervisor, the result set will include two rows for John, one for each position. Any data returned from the Employee table will be repeated for each row, but the data returned by the function will be specific to each row in the function’s results.

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/

https://codingsight.com/advanced-sql-cross-apply-and-outer-apply/