outer apply vs left join - How are these two different?

Question

I am Using SQL SERVER 2008 R2I just came across APPLY in SQL and loved how it solves query problems for so many cases,Many of the tables I was using 2 left join to get the result, I was able to get in 1 outer apply.I have a small amount of data in my local DB tables and after deployment the code is supposed to run on data at least 20 times bigger.I am concerned that outer apply might take longer than the 2 left join conditions for large amount of data,Can anyone tell how exactly the application works and how it will affect the performance in very large data? If possible some proportional relations with size of each table like proportional to n1^1 or n1^2 ... where n1 is the number of rows in table 1.Here is the query with 2 left joinselect EC.*,DPD.* from Table1 eC left join ( select member_id,parent_gid,child_gid,LOB,group_gid,MAX(table2_sid) mdsid from Table2 group by member_id,parent_gid,child_gid,LOB,group_gid ) DPD2 on DPD2.parent_gid = Ec.parent_gid AND DPD2.child_gid = EC.child_gid AND DPD2.member_id = EC.member_id AND DPD2.LOB = EC.default_lob AND DPD2.group_gid = EC.group_gid left join Table2 dpd on dpd.parent_gid = dpd2.parent_gid and dpd.child_gid = dpd2.child_gid and dpd.member_id = dpd2.member_id and dpd.group_gid = dpd2.group_gid and dpd.LOB = dpd2.LOB and dpd.table2_sid = dpd2.mdsidHere is the query with outer applyselect * from Table1 ec OUTER APPLY ( select top 1 grace_begin_date,retroactive_begin_date,Isretroactive from Table2 DPD where DPD.parent_gid = Ec.parent_gid AND DPD.child_gid = EC.child_gid AND DPD.member_id = EC.member_id AND DPD.LOB = EC.default_lob AND DPD.group_gid = EC.group_gid order by DPD.table2_sid desc ) DPD

Ananya Pawar · Accepted Answer

outer apply vs left join The first query may run parallel by only one request to sql server. It fetched all records and gave output based on filter criteria. But in the case of the second one it runs row by row and for each row Table2 will be scanned and appended to the result. if your outer query has less record then Second one is better(OUTER APPLY). But if the first query may get more data then you should use the first one.

Ranjana Admin · Answer

OUTER APPLY and LEFT JOIN are two different SQL operations that can sometimes be used to achieve similar results, but they have distinct behaviors and use cases. Here’s a detailed comparison and explanation of their differences:

LEFT JOIN

A LEFT JOIN is used to combine rows from two tables based on a related column between them. It returns all rows from the left table (the first table listed), and the matched rows from the right table (the second table listed). If there is no match, the result is NULL on the side of the right table.

Syntax:
SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;

Example:

  SELECT employees.name, departments.nameFROM employeesLEFT JOIN departmentsON employees.department_id = departments.id;

This query returns all employees and the names of their departments. If an employee is not assigned to a department, the department name will be NULL.

OUTER APPLY

OUTER APPLY is similar to a LEFT JOIN, but it is particularly useful when the right side of the join is a table-valued function or a subquery that needs to be evaluated for each row of the left table. It returns all rows from the left table, and the results of the table-valued function or subquery for each row of the left table. If the function or subquery does not return any results for a row, NULL values are produced.

Syntax:

  SELECT columnsFROM left_tableOUTER APPLY (subquery or table-valued function) AS alias;

Example:

  SELECT e.name, d.nameFROM employees eOUTER APPLY (    SELECT TOP 1 name    FROM departments d    WHERE d.id = e.department_id) d;

This query returns all employees and the name of their department. If an employee does not have a matching department, the department name will be NULL. The OUTER APPLY evaluates the subquery for each row of the employees table.

Ranjana Admin · Answer

If you're trying to understand the difference between OUTER APPLY and LEFT JOIN in SQL, you're not alone! Both are used to combine data from two tables or queries, but they behave differently and are suitable for different use cases. Here's how they differ:

1. Basic Purpose

LEFT JOIN: Combines rows from two tables based on a condition. If no matching row is found in the right table, NULLs are returned.
OUTER APPLY: Similar to a LEFT JOIN, but is typically used when joining with a table-valued function or a subquery that depends on the left table's data.

2. Handling of Derived Data

LEFT JOIN: Works with static tables or views and doesn’t dynamically evaluate data row by row.
OUTER APPLY: Evaluates the right-hand side for each row of the left-hand side, which makes it suitable for scenarios where you need row-dependent results, such as calling table-valued functions or using correlated subqueries.

3. Performance

LEFT JOIN: Often faster when dealing with static tables, as the query engine optimizes it better.
OUTER APPLY: Can be slower since it processes the right-side query for every row on the left side, but it’s more flexible.

4. Examples

LEFT JOIN Example:

SELECT a.*, b.*
FROM TableA a
LEFT JOIN TableB b ON a.ID = b.ID;

Joins two static tables based on a common column.

OUTER APPLY Example:

SELECT a.*, b.*
FROM TableA a
OUTER APPLY (SELECT TOP 1 * FROM TableB b WHERE b.ID = a.ID) b;

Fetches row-specific data dynamically, such as the first matching row.

5. When to Use Each

Use LEFT JOIN when working with static relationships between two tables.
Use OUTER APPLY when dealing with dynamic, row-specific queries or table-valued functions.

By understanding these key differences, you can decide which one to use depending on your scenario!

outer apply vs left join - How are these two different?

About this question

Your answer

3 Answers

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!