Microsoft SQL has two variants i.e. CROSS APPLY and OUTER APPLY. Both operators are in SQL used to perform table-valued function /correlated subqueries against rows in another table.
CROSS APPLY
- It is similar to an INNER JOIN i.e. It returns rows from the left table that have a matching row in the right table.
- No rows in the left table are excluded, even if there’s no match in the right side. However, for those rows, the columns from the right side will be NULL.
- You can use CROSS APPLY when you need data where there’s a match on both sides, similar to an INNER JOIN.
OUTER APPLY
- It is similar to a LEFT JOIN i.e. It returns all rows from the left table regardless of whether there’s a matching row in the right side.
- It retrieves all records from both sides, even if there’s no match.
- Use OUTER APPLY when you want all rows from the left table, even if there’s no corresponding data in the right table (like a LEFT JOIN).
- If a simple JOIN can achieve the same result, it’s generally preferred for better performance.
Points to consider:
- Both APPLY operators can be used with table-valued functions and correlated subqueries.
- APPLY operators often offer more flexibility than JOINs, but can be less performant.
sql.tech.blog 