Cross Apply and Outer Apply

Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.

An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.

(Left Side Table – Project Table)

(Right Side Table – Calendar Table)

Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.

Here is the query;

SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate 
FROM Project p
CROSS APPLY
(
	SELECT FiscalQuarterEndDate 
	FROM FiscalCalendar calendar
	WHERE 1=1
	AND p.ProjectEndDate 
	BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1

Here is the result;

ProjectKey           ProjectEndDate                 FiscalQuarterEndDate

If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).

If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.

Resources

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

Add to favorites