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

4.8K Asked by ananyaPawar in SQL Server , Asked on May 24, 2024

I am Using SQL SERVER 2008 R2

I 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 join

select 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.mdsid
Here is the query with outer apply
select * 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

Answered by Amit verma

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.

Your Answer

Answer (1)

Ranjana

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.

5 Months