Using OUTER APPLY to Resolve TOP 1 with LEFT JOIN Issues in SQL Server

Introduction

In SQL Server queries, developers often need to join tables and retrieve only the first matching row based on a specific order. A common approach is to use a LEFT JOIN with a subquery containing TOP 1 and ORDER BY. However, as illustrated in the provided Q&A data, this can lead to unexpected results, such as consistently returning null values. The original query uses a LEFT JOIN with a subquery that selects the top 1 row from the dps_usr_markers and dps_markers tables, ordered by creation_date. The issue arises because the subquery in the LEFT JOIN cannot access columns from the outer table (e.g., u.id) to filter results, causing it to return a single arbitrary row that may not match the correct profile_id, resulting in null values upon joining.

Solution: Implementing OUTER APPLY

To resolve this, the accepted answer recommends using OUTER APPLY instead of LEFT JOIN. OUTER APPLY allows the inner query to reference columns from the outer table, enabling correlated queries. This ensures that for each row in the dps_user table, the subquery fetches the first matching row based on profile_id. Unlike LEFT JOIN, which executes the subquery independently before joining, OUTER APPLY evaluates the subquery dynamically for each row of the outer table, providing the necessary correlation.

Code Example

The modified query is as follows:

SELECT u.id, mbg.marker_value 
FROM dps_user u
OUTER APPLY 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (<NOLOCK>)
         INNER JOIN dps_markers m (<NOLOCK>) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     WHERE um.profile_id=u.id 
     ORDER BY m.creation_date
    ) AS MBG
WHERE u.id = 'u162231993';

In this query, the WHERE clause inside the subquery references u.id, made possible by OUTER APPLY. This ensures that only relevant rows are considered for each user, preventing null returns.

In-Depth Comparison

The key difference between OUTER APPLY and LEFT JOIN lies in their execution mechanisms. LEFT JOIN processes the subquery independently and then joins the results, whereas OUTER APPLY evaluates the subquery per row of the outer table, allowing references to outer columns. This correlation is critical when filtering based on outer table columns. Other potential solutions include using the ROW_NUMBER() function with common table expressions (CTEs) or CROSS APPLY for inner joins, but OUTER APPLY is the most straightforward for this specific case, as it maintains left join semantics while supporting correlation.

Conclusion

By employing OUTER APPLY, one can effectively address the limitations of LEFT JOIN when joining with the first matching row in correlated subqueries. This approach ensures accurate query results while enhancing code readability and performance, making it a valuable technique in SQL Server optimization.