Differences between the JOIN and APPLY operators

The JOIN and APPLY operators in SQL Server are used to combine data from multiple tables or queries, but they serve different purposes and have distinct use cases. Here's a detailed comparison:

Key Differences Between JOIN and APPLY

Aspect	JOIN	APPLY
Purpose	Combines rows from two tables based on a condition.	Invokes a table-valued function or subquery for each row of the outer query.
Use Case	Used when combining tables with defined relationships.	Used for invoking table-valued functions or subqueries that depend on each row of the outer query.
Types	INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.	CROSS APPLY (like INNER JOIN) and OUTER APPLY (like LEFT JOIN).
Output Rows	Depends on matching conditions between tables.	Can include rows where the function or subquery produces results (CROSS APPLY) or doesn’t (OUTER APPLY).
Table-Valued Functions	Cannot invoke a table-valued function dynamically for each row.	Can dynamically invoke a table-valued function or subquery for each row of the outer query.
Performance	Typically more efficient for straightforward table joins.	May be slower, especially if the subquery or function is computationally expensive.
NULL Handling	Rows are included or excluded based on the join type.	CROSS APPLY excludes unmatched rows, while OUTER APPLY includes them with NULL for unmatched rows.

JOIN Overview

The JOIN operator combines rows from two tables based on a common condition, such as matching keys.

Example of JOIN

-- INNER JOIN example: Combines Customers and Orders based on CustomerID

SELECT C.CustomerName, O.OrderID, O.OrderDate

FROM Customers C

INNER JOIN Orders O ON C.CustomerID = O.CustomerID;

APPLY Overview

The APPLY operator applies a table-valued function or subquery to each row of the outer query. It is especially useful for querying data that depends on each row in the outer table.

CROSS APPLY

Behaves like an INNER JOIN; only includes rows where the function/subquery produces results.

OUTER APPLY

Behaves like a LEFT JOIN; includes rows even if the function/subquery produces no results, filling columns with NULL.

Example of APPLY

-- CROSS APPLY example: Calls a table-valued function for each Customer

SELECT C.CustomerName, O.OrderID, O.OrderDate

FROM Customers C

CROSS APPLY dbo.GetRecentOrders(C.CustomerID) O;

-- OUTER APPLY example: Includes Customers with no recent orders

SELECT C.CustomerName, O.OrderID, O.OrderDate

FROM Customers C

OUTER APPLY dbo.GetRecentOrders(C.CustomerID) O;

When to Use JOIN

When you are combining tables with well-defined relationships (e.g., foreign keys).
When your goal is straightforward data combination without per-row calculations or dynamic subqueries.

When to Use APPLY

When you need to call a table-valued function or perform a subquery dynamically for each row in the outer query.
When your query logic depends on row-by-row evaluation (e.g., filtering, calculations, or retrieving related data dynamically).

Comparison Example

Using JOIN

-- Get Customers and their Orders using JOIN

SELECT C.CustomerName, O.OrderID, O.OrderDate

FROM Customers C

LEFT JOIN Orders O ON C.CustomerID = O.CustomerID;

Using APPLY

-- Get Customers and their most recent order using APPLY

SELECT C.CustomerName, O.OrderID, O.OrderDate

FROM Customers C

OUTER APPLY (

SELECT TOP 1 OrderID, OrderDate

FROM Orders

WHERE Orders.CustomerID = C.CustomerID

ORDER BY OrderDate DESC

) O;

Conclusion

JOIN is ideal for combining data from multiple tables based on relationships.
APPLY is more flexible for invoking row-dependent logic, like table-valued functions or dynamic subqueries.

JOIN:

It combines rows from two tables based on a related column and a specified condition. It works on static sets of data and does not evaluate row by row logic.
Types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.
We can use JOIN when we want to combine data from two tables based on relationships or conditions.
It works on predefined sets of data from two tables. Both sides are static and fully evaluated before joining.
JOIN is generally faster because it deals with static sets and doesn't require row by row evaluation.

APPLY:

It applies a table-valued function or a derived table expression to each row from the left table. It evaluates row by row, making it useful for scenarios requiring dynamic or correlated logic.
Types of APPLY: CROSS APPLY and OUTER APPLY.
We can use APPLY when we need to call a table-valued function or compute results dynamically for each row of the left input.
It evaluates the right side expression dynamically for each row of the left table, allowing for row specific logic or computations.
APPLY may be slower than JOIN in cases where the table valued function is complex or requires row by row evaluation. However, it is more powerful when working with functions that return a variable number of rows.