Differences between the JOIN and APPLY operators

Question

What are the differences between the JOIN and APPLY operators?

Shubham Sidnale · Answer

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.

Amit Mohanty · Answer

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.